IMPDP实现不同用户之间的数据迁移.doc

【IMPDP】实现不同用户之间的数据迁移REMAP_SCHEMA参数众所周知，IMP工具的FROMUSER和TOUSER参数可以实现将一个用户的的数据迁移到另外一个用户。同样的功能在IMPPDP工具中如何得以体现呢？答案就是：使用IMPPDP的REMAP_SCHEMA参数实现。简单演示一下，供参考。任务：将sec用户中的数据迁移到secooler用户。1.分别确认sec和secooler用户下的表和数据情况1）sec用户下有一张T表，含有24360行数据sysora10g conn sec/secConnected.secora10g select * from tab;TNAME TABTYPE CLUSTERID- - -T TABLEsecora10g select count(*) from t; COUNT(*)- 243602）确认secooler用户不包含表Tsecoolerora10g conn secooler/secoolerConnected.secoolerora10g select * from tab;no rows selected2.创建目录对象expdp_dirsysora10g create or replace directory expdp_dir as /expdp;Directory created.3.将目录对象expdp_dir的读写权限授权给sec和secooler用户sysora10g grant read,write on directory expdp_dir to sec;Grant succeeded.sysora10g grant read,write on directory expdp_dir to secooler;Grant succeeded.4.生成sec的备份文件ora10gsecDB /expdp$ expdp sec/sec directory=expdp_dir dumpfile=date +%Y%m%d%H%M%S_sec.dmp logfile=date +%Y%m%d%H%M%S_sec.logExport: Release .0 - 64bit Production on Thursday, 01 April, 2010 10:29:17Copyright (c) 2003, 2005, Oracle. All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release .0 - 64bit ProductionWith the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine optionsStarting SEC.SYS_EXPORT_SCHEMA_01: sec/* directory=expdp_dir dumpfile=20100401102917_sec.dmp logfile=20100401102917_sec.logEstimate in progress using BLOCKS method.Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATATotal estimation using BLOCKS method: 3 MBProcessing object type SCHEMA_EXPORT/USERProcessing object type SCHEMA_EXPORT/SYSTEM_GRANTProcessing object type SCHEMA_EXPORT/ROLE_GRANTProcessing object type SCHEMA_EXPORT/DEFAULT_ROLEProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/TABLE/TABLEProcessing object type SCHEMA_EXPORT/TABLE/INDEX/INDEXProcessing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINTProcessing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICSProcessing object type SCHEMA_EXPORT/TABLE/COMMENT. . exported SEC.T 2.259 MB 24360 rowsMaster table SEC.SYS_EXPORT_SCHEMA_01 successfully loaded/unloaded*Dump file set for SEC.SYS_EXPORT_SCHEMA_01 is: /expdp/20100401102917_sec.dmpJob SEC.SYS_EXPORT_SCHEMA_01 successfully completed at 10:29:20生成的备份文件信息如下：ora10gsecDB /expdp$ ls -l *sec.dmp-rw-r- 1 oracle oinstall 2.5M Apr 1 10:29 20100401102917_sec.dmp5.使用IMPDP的REMAP_SCHEMA参数实现secooler用户的数据导入ora10gsecDB /expdp$ impdp secooler/secooler directory=expdp_dir dumpfile=20100401102917_sec.dmp logfile=20100401102917_sec_impdp.log REMAP_SCHEMA=sec:secoolerImport: Release .0 - 64bit Production on Thursday, 01 April, 2010 10:32:10Copyright (c) 2003, 2005, Oracle. All rights reserved.Connected to: Oracle Database 10g Enterprise Edition Release .0 - 64bit ProductionWith the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine optionsMaster table SECOOLER.SYS_IMPORT_FULL_01 successfully loaded/unloadedStarting SECOOLER.SYS_IMPORT_FULL_01: secooler/* directory=expdp_dir dumpfile=20100401102917_sec.dmp logfile=20100401102917_sec_impdp.log REMAP_SCHEMA=sec:secoolerProcessing object type SCHEMA_EXPORT/USERORA-31684: Object type USER:SECOOLER already existsProcessing object type SCHEMA_EXPORT/SYSTEM_GRANTProcessing object type SCHEMA_EXPORT/ROLE_GRANTProcessing object type SCHEMA_EXPORT/DEFAULT_ROLEProcessing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMAProcessing object type SCHEMA_EXPORT/TABLE/TABLEProcessing object type SCHEMA_EXPORT/TABLE/TABLE_DATA. . imported SECOOLER.T 2.259 MB 24360 rowsJob SECOOLER.SYS_IMPORT_FULL_01 completed with 1 error(s) at 10:32:12OK，迁入任务完成。6.确认最后的迁移迁移成果连接到secooler用户确认T表及其中的数据是否已经完成导入。secora10g conn secooler/secoolerConnected.secoolerora10g select * from tab;TNAME TABTYPE CLUSTERID- - -T TABLEsecoolerora10g select count(*) from t; COUNT(*)- 24360OK，搞定。7.进一步参考资料最好的参考资料就是Oracle的官方文档，参考链接如下：/docs/cd/B19306_01/server.102/b14215/dp_import.htm#sthref340便于参考，copy一份在此：REMAP_SCHEMADefault: nonePurposeLoadsall objects from the source schema into a target schema.Syntax and DescriptionREMAP_SCHEMA=source_schema:target_schemaMultipleREMAP_SCHEMAlines can be specified, but the source schema must be different for each one. However, different source schemas can map to the same target schema. The mapping may not be 100 percent complete, because there are certain schema references that Import is not capable of finding. For example, Import will not find schema references ded within the body of definitions of types, views, procedures, and packages.If the schema you are remapping to does not already exist, the import operation creates it, provided the dump file set contains the necessaryCREATEUSERmetadata for the source schema and you are importing with enough privileges. For example, the following Export commands would create the dump file sets with the necessary metadata to create a schema, because the userSYSTEMhas the necessary privileges: expdp SYSTEM/passwordSCHEMAS=hr expdp SYSTEM/passwordFULL=yIf your dump file set does not contain the metadata necessary to create a schema, or if you do not have privileges, then the target schema must be created before the import operation is performed. This is because the unprivileged dump files do not contain the necessary information for the import to create the schema automatically.If the import operation does create the schema, then after the import is complete, you must assign it a valid password in order to connect to it. The SQL statement to do this, which requires privileges, is:SQL ALTER USER schema_name IDENTIFIED BY new_pswdRestrictionsUnprivileged users can perform. schema remaps only if their schema is the target schema of the remap. (Privileged users can perform. unrestricted schema remaps.)For example,SCOTTcan remap hisBLAKEs objects toSCOTT, butSCOTTcannot remapSCOTTs objects toBLAKE.ExampleSuppose that you execute the following Export and Import commands to remap thehrschema into thescottschema: expdp SYSTEM/passwordSCHEMAS=hr DIRECTORY=dpump_dir1 DUMPFILE=hr.dmp impdp SYSTEM/passwordDIRECTORY=dpump_dir1 DUMPFILE=hr.dmpREMAP_SCHEMA=hr:scottIn this example, if userscottalready exists before the import, then the ImportREMAP_SCHEMAcommand will add objects