需求:把生产上以某些字母开头的表导入到测试环境的其它用户中。
实现过程:
1.expdp导出表
Import: Release 11.2.0.3.0 - Production on Mon Nov 12 11:32:47 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options
ORA-39002: invalid operation
ORA-39166: Object liangwei.prod_201203_26 was not found.
导入过程中,只导入指定表出现ORA-39002,ORA-39166错误。
如果impdp使用remap_schema,且指定要导入的表时,要指定表的schema。
$ impdp username/password directory=DPUMPDIR dumpfile=scott220.DMP tables=scott.prod_201203_26 remap_schema=scott:liangwei logfile=impdp.log
如果impdp使用remap_schema,导入dmp的所有数据时则不需要指定schema。
$ impdp username/password directory=DPUMPDIR dumpfile=scott220.DMP remap_schema=scott:liangwei logfile=impdp.log
参考文档:MOS [ID 1235495.1]
DataPump Import (IMPDP) With REMAP_SCHEMA Fails With Errors ORA-39002 ORA-39166 [ID 1235495.1]
实现方法:
1.在生产环境使用expdp的模糊导出功能,先导出这些表。
2.在测试使用impdp的remap_schema功能将这些表导入到测试环境的其它用户。实现过程:
1.expdp导出表
$ expdp scott/tiger DIRECTORY=DPUMPDIR DUMPFILE=scott220.DMP tables=prod% JOB_NAME=MYJOB3 LOGFILE=scottEXP.LOG
2.impdp导入表
$ impdp username/password directory=DPUMPDIR dumpfile=scott220.DMP tables=prod_201203_26 remap_schema=scott:liangwei logfile=impdp.logImport: Release 11.2.0.3.0 - Production on Mon Nov 12 11:32:47 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Data Mining and Real Application Testing options
ORA-39002: invalid operation
ORA-39166: Object liangwei.prod_201203_26 was not found.
导入过程中,只导入指定表出现ORA-39002,ORA-39166错误。
如果impdp使用remap_schema,且指定要导入的表时,要指定表的schema。
$ impdp username/password directory=DPUMPDIR dumpfile=scott220.DMP tables=scott.prod_201203_26 remap_schema=scott:liangwei logfile=impdp.log
如果impdp使用remap_schema,导入dmp的所有数据时则不需要指定schema。
$ impdp username/password directory=DPUMPDIR dumpfile=scott220.DMP remap_schema=scott:liangwei logfile=impdp.log
参考文档:MOS [ID 1235495.1]
DataPump Import (IMPDP) With REMAP_SCHEMA Fails With Errors ORA-39002 ORA-39166 [ID 1235495.1]