最近在写同步环境数据的脚本，写完了与大家分享一下：
    由于我们的线上环境中有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';