在目标库上建立数据泵目录
create directory dump_dir as '/oracle/ppp';
grant read,write on directory dump_dir to user;
在源库上导出数据:
expdp system/oracle directory=dump_dir dumpfile=user.dmp schemas=user logfile=user.log
导出表结构:
impdp system/oracle directory=dump_dir dumpfile=user.dmp sqlfile=user.sql logfile=userimp.log
在目标库上建立表
SQL>@/oracle/ppp/user.sql
导入数据前关闭约束和触发器:
SQL>set heading off
SQL>select 'alter table '||table_name||' disable constraint '||constraint_name||';' from user_constraints;
SQL>alter table table_name disable constraint constraint_name;
SQL>select 'alter trigger '||trigger_name||' disable;' from user_triggers;
SQL>alter trigger trigger_name disable;
导入数据:
export ORACLE_SID=SID
$impdp system/oracle directory=dump_dir dumpfile=javaoanew.dmp table_exists_action=append logfile=javaoaimpnew.log
导入数据后打开约束和触发器:
SQL>set heading off
SQL>select 'alter table '||table_name||' enable novalidate constraint '||constraint_name||';' from user_constraints;
SQL>alter table table_name enable novalidate constraint constraint_name;
SQL>select 'alter trigger '||trigger_name||' enable;' from user_triggers;
SQL>alter trigger trigger_name enable;
连接应用测试,一切OK.
create directory dump_dir as '/oracle/ppp';
grant read,write on directory dump_dir to user;
在源库上导出数据:
expdp system/oracle directory=dump_dir dumpfile=user.dmp schemas=user logfile=user.log
导出表结构:
impdp system/oracle directory=dump_dir dumpfile=user.dmp sqlfile=user.sql logfile=userimp.log
在目标库上建立表
SQL>@/oracle/ppp/user.sql
导入数据前关闭约束和触发器:
SQL>set heading off
SQL>select 'alter table '||table_name||' disable constraint '||constraint_name||';' from user_constraints;
SQL>alter table table_name disable constraint constraint_name;
SQL>select 'alter trigger '||trigger_name||' disable;' from user_triggers;
SQL>alter trigger trigger_name disable;
导入数据:
export ORACLE_SID=SID
$impdp system/oracle directory=dump_dir dumpfile=javaoanew.dmp table_exists_action=append logfile=javaoaimpnew.log
导入数据后打开约束和触发器:
SQL>set heading off
SQL>select 'alter table '||table_name||' enable novalidate constraint '||constraint_name||';' from user_constraints;
SQL>alter table table_name enable novalidate constraint constraint_name;
SQL>select 'alter trigger '||trigger_name||' enable;' from user_triggers;
SQL>alter trigger trigger_name enable;
连接应用测试,一切OK.