每日同步環境SHELL指令碼

    最近在寫同步環境資料的指令碼，寫完了與大家分享一下：
    由於我們的線上環境中有lob欄位，而兩個庫的表空間名稱又不一樣，所以在drop物件以後，imp時會有問題，因為lob欄位的storage引數中寫死了表空間的名稱，所以我考慮用truncate table的方法來同步資料，不動表結構。

一、 建立syn.sh指令碼
export ORACLE_BASE=/oracle
export ORACLE_HOME=$ORACLE_BASE/product/10.2/db
export PATH=/usr/bin:/etc/:/usr/sbin:/usr/ucb:/usr/local/bin:$ORACLE_HOME/bin:$ORA_CRS_HOME/bin:/usr/bin/X11:/sbin:/oracle/soft/patch/OPatch:$
PATH
export NLS_LANG=American_America.ZHS16GBK

--1.禁用外來鍵約束和觸發器
######## disable foreign&trigger constraint ##########
sqlplus ecc_view/ecc@devdb1  @/expdata/syn_script/disable_constraint1.sql
sqlplus ecc_view/ecc@devdb1  @/expdata/syn_script/disable_constraintc.sql

--2.truncate table
########syn tables##########
sqlplus ecc_view/ecc@devdb1  @/expdata/syn_script/trunc1_exe1.sql
sqlplus ecc_view/ecc@devdb1  @/expdata/syn_script/trunc1_exec.sql

--3.imp 資料,ignore=y
########imp data##########
imp ecc_view/ecc@devdb1  file=/expdata/exp-tmp/ecc_view1.dmp parfile=/expdata/syn_script/table1.par log=/expdata/exp-tmp/ecc_view1.log ignore=y grants=n buffer=9999999 

--4.重新編譯儲存過程
########recompile procedure##########
sqlplus ecc_view/ecc@devdb1  @/expdata/syn_script/execompile1.sql
sqlplus ecc_view/ecc@devdb1  @/expdata/syn_script/execompilec.sql

--5.啟用外來鍵約束和觸發器
######## enable foreign&trigger constraint ##########
sqlplus ecc_view/ecc@devdb1  @/expdata/syn_script/enable_constraint1.sql
sqlplus ecc_view/ecc@devdb1  @/expdata/syn_script/enable_constraintc.sql

二、附加指令碼：
1.disable_constraint1.sql

set linesize 1024
set pagesize 1024
set feedback off
set termout off
set heading off
spool /expdata/syn_script/disable_constraintc.sql

select ' alter table '||a.table_name||' disable constraint '||constraint_name||';' from user_constraints a where a.constraint_type = 'R';
select ' ALTER TRIGGER '||a.trigger_name||' DISABLE ;' from user_triggers a ;
select 'exit' from dual;
spool off
exit

2.trunc1_exe1.sql

set linesize 1024
set pagesize 1024
set feedback off
set termout off
set heading off
spool /expdata/syn_script/trunc1_exec.sql

select 'truncate table cpfbos.'||table_name||';' from user_tables where table_name ;
select 'exit' from dual;
spool off
exit

3.execompile1.sql

set linesize 1024
set pagesize 1024
set feedback off
set termout off
set heading off
spool /expdata/syn_script/execompilec.sql

select 'ALTER '||A.OBJECT_TYPE||' '||A.OBJECT_NAME||' COMPILE ;' from user_objects a where A.status <> 'VALID' and a.object_type = 'PROCEDURE' ORDER BY A.OBJECT_TYPE DESC; 
select 'exit' from dual;
spool off
exit

4.enable_constraint1.sql

set linesize 1024
set pagesize 1024
set feedback off
set termout off
set heading off
spool /expdata/syn_script/enable_constraintc.sql

select ' alter table '||a.table_name||' enable constraint '||constraint_name||';' from user_constraints a where a.constraint_type = 'R';
select ' ALTER TRIGGER '||a.trigger_name||' ENABLE ;' from user_triggers a;
select 'exit' from dual;
spool off
exit

5.匯出線上sequence指令碼

select
'create sequence schema_name.'|| SEQUENCE_NAME || ' minvalue '||MIN_VALUE||' maxvalue '||MAX_VALUE||' start with '||LAST_NUMBER||' increment by '||INCREMENT_BY||' cache '||CACHE_SIZE||' ;'
from dba_sequences where SEQUENCE_OWNER='&your_schema_name';