/*第1步：创建数据表空间 */
create tablespace TBS_EA_ACCOUNT
logging
datafile 'E:\tablespace\TBS_EA_ACCOUNT.dbf'
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;
create tablespace TS_KAMS
logging
datafile 'E:\tablespace\TS_KAMS.dbf'
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;
CREATE temporary tablespace TBS_TEMP_EA_MOFS
tempfile 'E:\tablespace\TBS_TEMP_EA_MOFS.dbf'
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;
--DROP TABLESPACE TBS_TEMP_EA_MOFS;
create tablespace TBS_EA_CMP
logging
datafile 'E:\tablespace\TBS_EA_CMP.dbf'
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;
create tablespace TBS_EA_CLEAR
logging
datafile 'E:\tablespace\TBS_EA_CLEAR.dbf'
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;
create tablespace TBS_EA_IST
logging
datafile 'E:\tablespace\TBS_EA_IST.dbf'
size 50m
autoextend on
next 50m maxsize 20480m
extent management local;
/*删除表空间 */
drop tablespace TBS_EA_IST including contents and datafiles ;
/*第3步：创建用户并指定表空间 */
create user CITICSUTIL identified by CITICSUTIL
default tablespace USERS
temporary TABLESPACE TBS_TEMP_EA_MOFS ;
create user ea_ist identified by ea_ist
default tablespace TBS_EA_IST ;
create user ES_DBA identified by ES_DBA
default tablespace TBS_TEMP_EA_MOFS ;
create user ES_EDC identified by ES_EDC
default tablespace TBS_TEMP_EA_MOFS ;
create user EA_TRUST identified by EA_TRUST
default tablespace TBS_TEMP_EA_MOFS ;
create user EA_MDM identified by EA_MDM
default tablespace TBS_TEMP_EA_MOFS ;
create user EA_HOLD identified by EA_HOLD
default tablespace TBS_TEMP_EA_MOFS ;
create user EA_MOFS identified by EA_MOFS
default tablespace TBS_TEMP_EA_MOFS ;
create user E_TASK identified by E_TASK
default tablespace TBS_TEMP_EA_MOFS ;
create user DMSYS identified by DMSYS
default tablespace TBS_TEMP_EA_MOFS ;
create user EI_OPEN identified by EI_OPEN
default tablespace TBS_TEMP_EA_MOFS ;
create user EDM_BASE identified by EDM_BASE
default tablespace TBS_TEMP_EA_MOFS ;
create user PRD2 identified by PRD2
default tablespace TBS_TEMP_EA_MOFS ;
create user CITICSCCS identified by CITICSCCS
default tablespace TBS_TEMP_EA_MOFS ;
create user BF_ZX_PTC identified by BF_ZX_PTC
default tablespace TBS_TEMP_EA_MOFS ;
create user HS_FUND identified by HS_FUND
default tablespace TBS_TEMP_EA_MOFS ;
/*第4步：给用户授予权限 */
grant connect,resource,dba ,
CREATE SESSION,
CREATE ANY SEQUENCE,
CREATE ANY TABLE,
ALTER ANY TABLE,
CREATE ANY INDEX,
DELETE ANY TABLE,
INSERT ANY TABLE,
SELECT ANY TABLE,
UNLIMITED TABLESPACE,
EXECUTE ANY procedure,
UPDATE ANY TABLE,
QUERY REWRITE,
CREATE ANY VIEW to CITICSUTIL ;
/* 导入导出命令 */
ip导出方式： EXP ea_ist/ea_ist@EOSP210 file=E:/tablespace/ea_ist.dmp full=y ROWS=n
EXP CITICSUTIL/CITICSUTIL@EOSP210 file=E:/tablespace/citicsutil.dmp full=y ROWS=n
imp CITICSUTIL/CITICSUTIL@ORCL file=E:/tablespace/citicsutil.dmp full=y ignore=y LOG=E:/tablespace/citicsutil.log
imp ea_ist/ea_ist@ORCL file=E:/tablespace/ea_ist.dmp full=y ignore=y LOG=E:/tablespace/ea_ist.log
/*-------开启归档模式-------------------------------------------------------------------------*/
1、连接
windows系统：打开cmd窗口
Linux/Unix系统：打开终端
rman target / nocatalog;
sqlplus /nolog
conn / as sysdba;
2、开启归档
sqlplus>shutdown immediate;（启动归档前先要停止数据库）
sqlplus>startup mount;（数据库以mount方式启动）
startup mount exclusive;
sqlplus>alter database archivelog;（启动数据库归档）
--sqlplus>alter system set log_archive_dest="D:\oracle\flash_recovery_area" ; （改变归档日志路径，windows系统使用盘符）
sqlplus>alter database open;（打开数据库）
sqlplus>archive log list;（查看归档是否已经打开）
关闭归档
alter database noarchivelog;
/*-------oracle闪回-------*/
select log_mode,open_mode,flashback_on from v$database;
show parameter db_recovery;
show parameter db_flashback --缺省为分钟，即小时
shutdown immediate;
startup mount exclusive;
alter system set db_flashback_retention_target=30; --设定保留时间为半小时
alter database flashback on; --开启闪回数据库功能
select * from v$flashback_database_stat; --查看闪回
alter database open;
archive log list;
--=======================================================================================================
-- 查看并行度 show parameters parallel_thread
SELECT /*+parallel(t 4)*/
COUNT(*)
FROM ccs_clear_jour
WHERE settle_entity_id LIKE '%%'
---------------------------------------------------------------------------------------------------------------
STD_CMS_
collect_date NUMBER(8)
DECODE(A.BUSI_TYPE, '买', 'BUY', '卖', 'SELL', A.BUSI_TYPE) SUB_BUSI_TYPE,
DECODE(C.BUSI_UNIT_CODE,
'0001',
'81',
'0002',
'82',
'0005',
'85') CLEAR_OBJECT_CODE,
TO_DATE(A.EXCH_TIME, 'YYYY-MM-DD HH24:MI:SS') EXCH_TIME,
ABS(A.EXCH_PRICE * A.EXCH_QTY * B.CONTRACT_MULTIPLE *
NVL(B.PRICE_ORG, 1)) EXCH_AMT,
DECODE(A.BUSI_TYPE, '买', 1, -1) * ABS(A.EXCH_QTY) EXCH_QTY,
WHERE A.STATE <> '已撤销'
AND SUBSTR(CONT_CODE, 1, 5) = B.FUTURE_PRODUCT_CODE
AND TO_CHAR(TO_DATE(SUBSTR(A.EXCH_TIME, 1, 10), 'YYYY-MM-DD'),
'YYYYMMDD') = AN_I_CLEAR_DATE;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
AN_O_RET_CODE := -1;
AV_O_RET_MSG := PKG_PUB_UTILS.F_GET_ERR_MSG(SQLCODE, SQLERRM);
RAISE_APPLICATION_ERROR(-20008,
CASE WHEN SQLCODE = -20008 THEN '' ELSE
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE END,
TRUE);
RAISE;
--序列
create sequence S_CCS_CLEAR_TRADE_BUSI_JOUR_ID
minvalue 1
maxvalue 999999999999999999999999999
start with 140279
increment by 1
cache 10000; --序列
LPAD(S_CCS_CLEAR_TRADE_BUSI_JOUR_ID.NEXTVAL, 8, '0') 8位字符，左边补0
select * from user_sys_privs where privilege like upper('%DATABASE LINK%');
grant create public database link to citicsccs;
--创建dblink
create public database link E31
connect to citicsccs identified by citicsccs
using '(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.23.118.31)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = eosp)
)
)';