首页 > 代码库 > 使用数据泵将指定的表和序列还原到一个新的schema下
使用数据泵将指定的表和序列还原到一个新的schema下
使用数据泵将指定的表和序列还原到一个新的schema下
先创建测试users
-- Create the user
create user SCOTT
default tablespace USERS
temporary tablespace TEMP
profile DEFAULT
password expire
account lock;
-- Grant/Revoke role privileges
grant connect to SCOTT;
grant resource to SCOTT;
-- Grant/Revoke system privileges
grant unlimited tablespace to SCOTT;
alter user scott identified by scott
-- Create the user
create user hr identified by hr
default tablespace USERS
temporary tablespace TEMP
profile DEFAULT
password expire
account lock;
-- Grant/Revoke role privileges
grant connect to hr;
grant resource to hr;
-- Grant/Revoke system privileges
grant unlimited tablespace to hr;
查看scott下的对象信息
导出Scott
expdp system/oracle directory=expdp_dump dumpfile=expdp_scott_1017$rq.dmp logfile=expdp_scott_1017$rq.log schemas=scott;
导入指定表
测试1：使用tables
将Scott下的是TA1，TA2，TA3导入到hr下：
impdp system/oracle directory=expdp_dump dumpfile=expdp_scott_1017.dmp logfile=impdp_scott_1017.log remap_schema=scott:hr tables=scott.TA1,scott.TA2,scott.TA3;
查看导入hr下的对象
清除导入hr的对象
测试2：使用include
这种导入会提示ORA-31655：no data or metadata objects selected for job
无法导入表，需要测试1的tables导入
将Scott下的是TA1导入到hr下：
impdp system/oracle directory=expdp_dump dumpfile=expdp_scott_1017.dmp logfile=impdp_scott_1017.log remap_schema=scott:hr include=TABLE:\"=\‘scott.ta1\‘\";
将Scott下的是TA1，TA2，TA3导入到hr下：
impdp system/oracle directory=expdp_dump dumpfile=expdp_scott_1017.dmp logfile=impdp_scott_1017.log remap_schema=scott:hr include=TABLE:\"in \(\‘TA1\‘,\‘TA2\‘,\‘TA3\‘\)\";
导入指定序列
要导入制定列需要使用include，而且需要使用转义字符"\"
将Scott下的是SEQ_TA1_ID导入到hr下：
impdp system/oracle directory=expdp_dump dumpfile=expdp_scott_1017.dmp logfile=impdp_scott_1017.log remap_schema=scott:hr include=SEQUENCE:\"in \(\‘SEQ_TA1_ID\‘\)\";
将Scott下的是SEQ_TA2_ID，SEQ_TA3_ID导入到hr下：
impdp system/oracle directory=expdp_dump dumpfile=expdp_scott_1017.dmp logfile=impdp_scott_1017.log remap_schema=scott:hr include=SEQUENCE:\"in \(\‘SEQ_TA2_ID\‘,\‘SEQ_TA3_ID\‘\)\";
查看两次导入的对象，都成功
将Scott下的所有序列导入到hr下：
impdp system/oracle directory=expdp_dump dumpfile=expdp_scott_1017.dmp logfile=impdp_scott_1017.log remap_schema=scott:hr include=SEQUENCE;
查看导入的对象
当导入的对象指定前缀是也无法导入
impdp system/oracle directory=expdp_dump dumpfile=expdp_scott_1017.dmp logfile=impdp_scott_1017.log remap_schema=scott:hr include=SEQUENCE:\"in \(\‘scott.SEQ_TA1_ID\‘\)\";
下面尝试将表和序列一起导入，但是都不成功，只能导入序列，所以目前分开导入
impdp system/oracle directory=expdp_dump dumpfile=expdp_scott_1017.dmp logfile=impdp_scott_1017.log remap_schema=scott:hr include=SEQUENCE:\"in \(\‘SEQ_TA2_ID\‘,\‘SEQ_TA3_ID\‘\)\" tables=scott.TA1,scott.TA2,scott.TA3;
impdp system/oracle directory=expdp_dump dumpfile=expdp_scott_1017.dmp logfile=impdp_scott_1017.log remap_schema=scott:hr tables=scott.TA1,scott.TA2,scott.TA3 include=SEQUENCE:\"in \(\‘SEQ_TA2_ID\‘,\‘SEQ_TA3_ID\‘\)\";
impdp system/oracle directory=expdp_dump dumpfile=expdp_scott_1017.dmp logfile=impdp_scott_1017.log remap_schema=scott:hr include=SEQUENCE:\"in \(\‘SEQ_TA2_ID\‘,\‘SEQ_TA3_ID\‘\)\",include=TABLE:\"in \(\‘TA1\‘,\‘TA2\‘,\‘TA3\‘\)\"
impdp system/oracle directory=expdp_dump dumpfile=expdp_scott_1017.dmp logfile=impdp_scott_1017.log remap_schema=scott:hr include=SEQUENCE:\"in \(\‘SEQ_TA2_ID\‘,\‘SEQ_TA3_ID\‘\)\",TABLE:\"in \(\‘TA1\‘,\‘TA2\‘,\‘TA3\‘\)\"
impdp ORA-31655错误处理一例
http://www.oracleonlinux.cn/2012/02/impdp-ora-31655/
ORA-39001: invalid argument value ORA-39071: Value for EXCLUDE is badly formed.
http://www.codeweblog.com/ora-39001-invalid-argument-value-ora-39071-value-for-exclude-is-badly-formed/
expdp impdp中 exclude/include 的使用
http://blog.csdn.net/liqfyiyi/article/details/7248911
使用数据泵将指定的表和序列还原到一个新的schema下