Oracle使用数据泵 (expdp/impdp)实施迁移
|
Oracle使用数据泵 (expdp/impdp)实施迁移 实验环境： 1、导出环境：RedHat6.4+Oracle 11.2.0.4.0，利用数据库自带的scott示例用户进行试验测试。 Directory：wjq à /tmp/seiang_wjq
2、导入环境：Centos7.1+Oracle 12.2.0.1.0 Oracle12c默认没有scott用户 Directory：imp_wjq à /tmp/imp_comsys
一、导出数据： 特别注意：如果后续要导入的数据库版本低，所有导出命令就需要在后面加一个version=指定版本。例如11g -> 10g，假设10g具体版本为10.2.0.1，那么就加一个版本的参数version=10.2.0.1。 1. 首先需要创建Directory
注意：目录在系统上需要真实存在（mkdir /tmp/seiang_wjq），且有访问的权限。
2. 使用expdp导出用户数据 2.1 只导出scott用户的元数据，且不包含统计信息； [oracle@seiangwjq ~]$ expdp system directory=wjq schemas=scott content=metadata_onlyexclude=statistics dumpfile=scott_meta.dmp logfile=scott_meta.log Export: Release 11.2.0.4.0 - Production on Mon Apr 24 14:17:16 2017 Copyright (c) 1982,2011,Oracle and/or its affiliates. All rights reserved. Password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning,OLAP,Data Mining and Real Application Testing options Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** directory=wjq schemas=scott content=metadata_only exclude=statistics dumpfile=scott_meta.dmp logfile=scott_meta.log Processing object type SCHEMA_EXPORT/USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is: /tmp/seiang_wjq/scott_meta.dmp Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon Apr 24 14:17:48 2017 elapsed 0 00:00:21 2.2 只导出scott用户的数据； [oracle@seiangwjq ~]$ expdp system directory=wjq schemas=scott content=data_only dumpfile=scott_data.dmp logfile=scott_data.log Export: Release 11.2.0.4.0 - Production on Mon Apr 24 14:22:36 2017 Copyright (c) 1982,Data Mining and Real Application Testing options Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** directory=wjq schemas=scott content=data_only dumpfile=scott_data.dmp logfile=scott_data.log Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 192 KB . . exported "SCOTT"."DEPT" 5.929 KB 4 rows . . exported "SCOTT"."EMP" 8.562 KB 14 rows . . exported "SCOTT"."SALGRADE" 5.859 KB 5 rows . . exported "SCOTT"."BONUS" 0 KB 0 rows Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is: /tmp/seiang_wjq/scott_data.dmp Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon Apr 24 14:22:47 2017 elapsed 0 00:00:06 2.3 只导出scott用户下的emp，dept表及数据； [oracle@seiangwjq ~]$ expdp scott directory=wjq tables=emp,dept dumpfile=scott_emp_dept.dmp logfile=scott_emp_dept.log Export: Release 11.2.0.4.0 - Production on Mon Apr 24 14:25:37 2017 Copyright (c) 1982,Data Mining and Real Application Testing options ORA-39002: invalid operation ORA-39070: Unable to open the log file. ORA-39087: directory name WJQ is invalid 这里如果用scott用户导出，需要注意scott用户对于directory的权限问题：需要dba用户赋予scott用户read,write目录的权限。
[oracle@seiangwjq ~]$ expdp scott directory=wjq tables=emp,dept dumpfile=scott_emp_dept.dmplogfile=scott_emp_dept.log Export: Release 11.2.0.4.0 - Production on Mon Apr 24 14:28:18 2017 Copyright (c) 1982,Oracle and/or its affiliates. All rights reserved. Password: Connected to: Oracle Database 11g Enterprise Edition Release11.2.0.4.0 - 64bit Production With the Partitioning,Data Mining and Real ApplicationTesting options Starting "SCOTT"."SYS_EXPORT_TABLE_01": scott/******** directory=wjq tables=emp,deptdumpfile=scott_emp_dept.dmp logfile=scott_emp_dept.log Estimate in progress using BLOCKS method... Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 128 KB Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT . . exported "SCOTT"."DEPT" 5.929 KB 4 rows . . exported "SCOTT"."EMP" 8.562 KB 14 rows Master table "SCOTT"."SYS_EXPORT_TABLE_01"successfully loaded/unloaded ****************************************************************************** Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is: /tmp/seiang_wjq/scott_emp_dept.dmp Job "SCOTT"."SYS_EXPORT_TABLE_01" successfullycompleted at Mon Apr 24 14:28:35 2017 elapsed 0 00:00:09 2.4 只导出scott用户下的emp，dept表结构； [oracle@seiangwjq ~]$ expdp scott directory=wjq tables=emp,dept content=metadata_onlydumpfile=scott_emp_dept_meta.dmp logfile=scott_emp_dept_meta.log Export: Release 11.2.0.4.0 - Production on Mon Apr 24 14:34:07 2017 Copyright (c) 1982,deptcontent=metadata_only dumpfile=scott_emp_dept_meta.dmplogfile=scott_emp_dept_meta.log Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Master table "SCOTT"."SYS_EXPORT_TABLE_01"successfully loaded/unloaded ****************************************************************************** Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is: /tmp/seiang_wjq/scott_emp_dept_meta.dmp Job "SCOTT"."SYS_EXPORT_TABLE_01" successfullycompleted at Mon Apr 24 14:34:21 2017 elapsed 0 00:00:08 2.5 导出scott用户下所有的内容； [oracle@seiangwjq ~]$ expdp system directory=wjq schemas=scott dumpfile=scott_all.dmplogfile=scott_all.log Export: Release 11.2.0.4.0 - Production on Mon Apr 24 14:38:10 2017 Copyright (c) 1982,Data Mining and Real ApplicationTesting options Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** directory=wjq schemas=scottdumpfile=scott_all.dmp logfile=scott_all.log Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 192 KB Processing object type SCHEMA_EXPORT/USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT . . exported "SCOTT"."DEPT" 5.929 KB 4 rows . . exported "SCOTT"."EMP" 8.562 KB 14 rows . . exported "SCOTT"."SALGRADE" 5.859 KB 5 rows . . exported "SCOTT"."BONUS" 0 KB0 rows Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01"successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is: /tmp/seiang_wjq/scott_all.dmp Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfullycompleted at Mon Apr 24 14:38:30 2017 elapsed 0 00:00:16 2.6 并行导出scott用户下所有的内容； [oracle@seiangwjq ~]$ expdp system directory=wjq schemas=scott dumpfile=scott_all%U.dmplogfile=scott_all.log parallel=2 Export: Release 11.2.0.4.0 - Production on Mon Apr 24 14:44:04 2017 Copyright (c) 1982,Data Mining and Real ApplicationTesting options Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** directory=wjq schemas=scottdumpfile=scott_all%U.dmp logfile=scott_all.log parallel=2 Estimate in progress using BLOCKS method... Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA Total estimation using BLOCKS method: 192 KB Processing object type SCHEMA_EXPORT/USER . . exported "SCOTT"."DEPT" 5.929 KB 4 rows . . exported "SCOTT"."EMP" 8.562 KB 14 rows Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT . . exported "SCOTT"."SALGRADE" 5.859 KB 5 rows . . exported "SCOTT"."BONUS" 0 KB 0 rows Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01"successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is: /tmp/seiang_wjq/scott_all01.dmp /tmp/seiang_wjq/scott_all02.dmp Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfullycompleted at Mon Apr 24 14:44:27 2017 elapsed 0 00:00:15 3. 查询当前用户用到的表空间
二、导入数据 导入准备：将刚才从11g数据库导出的内容通过scp发送给12c
1. 首先需要创建Directory
2. 使用impdp导入用户数据2.1 导入scott用户的元数据，且不包含统计信息；[oracle@seiang ~]$ impdp system directory=imp_wjq dumpfile=scott_meta.dmplogfile=imp_scott_meta.log Import: Release 12.2.0.1.0 - Production on Mon Apr 24 15:26:30 2017 Copyright (c) 1982,2017,Oracle and/or its affiliates. All rights reserved. Password: Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0- 64bit Production Master table "SYSTEM"."SYS_IMPORT_FULL_01"successfully loaded/unloaded Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** directory=imp_wjqdumpfile=scott_meta.dmp logfile=imp_scott_meta.log Processing object type SCHEMA_EXPORT/USER Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Job "SYSTEM"."SYS_IMPORT_FULL_01" successfullycompleted at Mon Apr 24 15:27:15 2017 elapsed 0 00:00:20 2.2 导入scott用户的数据；只有在2.1导入元数据后才可以导入数据。 [oracle@seiang ~]$ impdp system directory=imp_wjq dumpfile=scott_data.dmplogfile=imp_scott_data.log Import: Release 12.2.0.1.0 - Production on Mon Apr 24 15:29:27 2017 Copyright (c) 1982,Oracle and/or its affiliates. All rights reserved. Password: Connected to: Oracle Database 12c Enterprise Edition Release12.2.0.1.0 - 64bit Production Master table "SYSTEM"."SYS_IMPORT_FULL_01"successfully loaded/unloaded Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/******** directory=imp_wjqdumpfile=scott_data.dmp logfile=imp_scott_data.log Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA . . imported "SCOTT"."DEPT" 5.929 KB 4 rows . . imported "SCOTT"."EMP" 8.562 KB 14 rows . . imported "SCOTT"."SALGRADE" 5.859 KB 5 rows . . imported "SCOTT"."BONUS" 0 KB 0 rows Job "SYSTEM"."SYS_IMPORT_FULL_01" successfullycompleted at Mon Apr 24 15:29:44 2017 elapsed 0 00:00:12 2.3 只导入scott用户下的emp表及数据；[oracle@seiang ~]$ impdp scott directory=imp_wjq tables=empdumpfile=scott_emp_dept.dmp logfile=imp_scott_emp.log Import: Release 12.2.0.1.0 - Production on Mon Apr 24 15:40:56 2017 Copyright (c) 1982,Oracle and/or its affiliates. All rights reserved. Password: Connected to: Oracle Database 12c Enterprise Edition Release12.2.0.1.0 - 64bit Production ORA-39002:invalid operation ORA-39070:Unable to open the log file. ORA-39087:directory name IMP_WJQ is invalid
因为在导入的时候没有给imp_wjq目录赋予read和write的权限，所以会出现上面的错误，下面就给imp_wjq目录授权：
[oracle@seiang ~]$ impdp scott directory=imp_wjq tables=emp dumpfile=scott_emp_dept.dmplogfile=imp_scott_emp.log Import: Release 12.2.0.1.0 - Production on Mon Apr 24 15:45:03 2017 Copyright (c) 1982,Oracle and/or its affiliates. All rights reserved. Password: Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0- 64bit Production Master table "SCOTT"."SYS_IMPORT_TABLE_01"successfully loaded/unloaded Starting "SCOTT"."SYS_IMPORT_TABLE_01": scott/******** directory=imp_wjq tables=empdumpfile=scott_emp_dept.dmp logfile=imp_scott_emp.log Processing object type TABLE_EXPORT/TABLE/TABLE ORA-39151:Table "SCOTT"."EMP" exists. All dependent metadata and datawill be skipped due to table_exists_action of skip Processing object type TABLE_EXPORT/TABLE/TABLE_DATA Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Job "SCOTT"."SYS_IMPORT_TABLE_01" completed with1 error(s) at Mon Apr 24 15:45:13 2017 elapsed 0 00:00:04 2.4 只导入scott用户下的emp,dept表结构；由于之前2.1、2.2、2.3导入的执行，所有的表都已成功导入，为了接下来的实验，我们把scott用户下存在的表都删掉；
[oracle@seiang ~]$ impdp scott directory=imp_wjq tables=emp,dept dumpfile=scott_emp_dept_meta.dmplogfile=imp_scott_emp_dept_meta.log Import: Release 12.2.0.1.0 - Production on Mon Apr 24 15:59:16 2017 Copyright (c) 1982,Oracle and/or its affiliates. All rights reserved. Password: Connected to: Oracle Database 12c Enterprise Edition Release12.2.0.1.0 - 64bit Production Master table "SCOTT"."SYS_IMPORT_TABLE_01"successfully loaded/unloaded Starting "SCOTT"."SYS_IMPORT_TABLE_01": scott/******** directory=imp_wjqtables=emp,dept dumpfile=scott_emp_dept_meta.dmplogfile=imp_scott_emp_dept_meta.log Processing object type TABLE_EXPORT/TABLE/TABLE Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Job "SCOTT"."SYS_IMPORT_TABLE_01" successfullycompleted at Mon Apr 24 15:59:22 2017 elapsed 0 00:00:02 由于导出就是emp,dept两张表，所以也可以不指定tables，以下两种写法在这里都是可以的： [oracle@seiang ~]$ impdp scott directory=imp_wjqdumpfile=scott_emp_dept_meta.dmp logfile=imp_scott_emp_dept_meta.log 或者 [oracle@seiang ~]$ impdp scott directory=imp_wjqdumpfile=scott_emp_dept_meta.dmp logfile=imp_scott_emp_dept_meta.log full=y
2.5 导入scott用户下所有的内容；如果是在2.4基础上直接导入，会因为emp,dept表已经存在导致导入过程中会由于table_exists_action参数的默认选项是skip，从而跳过emp，dept表数据的导入，如下： [oracle@seiang ~]$ impdp system directory=imp_wjq schemas=scottdumpfile=scott_all.dmp logfile=imp_scott_all.log Import: Release 12.2.0.1.0 - Production on Mon Apr 24 16:06:28 2017 Copyright (c) 1982,Oracle and/or its affiliates. All rights reserved. Password: Connected to: Oracle Database 12c Enterprise Edition Release12.2.0.1.0 - 64bit Production Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01"successfully loaded/unloaded Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/******** directory=imp_wjqschemas=scott dumpfile=scott_all.dmp logfile=imp_scott_all.log Processing object type SCHEMA_EXPORT/USER ORA-31684:Object type USER:"SCOTT" already exists Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE ORA-39151:Table "SCOTT"."EMP" exists. All dependent metadata and datawill be skipped due to table_exists_action of skip ORA-39151:Table "SCOTT"."DEPT" exists. All dependent metadata anddata will be skipped due to table_exists_action of skip Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA . . imported "SCOTT"."SALGRADE" 5.859 KB 5 rows . . imported "SCOTT"."BONUS" 0 KB 0 rows Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completedwith 3 error(s) at Mon Apr 24 16:06:38 2017 elapsed 0 00:00:05 所以这时我们想导入这些数据，可以加参数 table_exists_action，指定想要的选项。 这里选择truncate，即如果表存在，那么处理方式是truncate此表后导入文件中包含的数据。 [oracle@seiang ~]$ impdp system directory=imp_wjq schemas=scott table_exists_action=truncatedumpfile=scott_all.dmp logfile=imp_scott_all.log Import: Release 12.2.0.1.0 - Production on Mon Apr 24 16:17:44 2017 Copyright (c) 1982,Oracle and/or its affiliates. All rights reserved. Password: Connected to: Oracle Database 12c Enterprise Edition Release12.2.0.1.0 - 64bit Production Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01"successfully loaded/unloaded Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/******** directory=imp_wjqschemas=scott table_exists_action=truncate dumpfile=scott_all.dmplogfile=imp_scott_all.log Processing object type SCHEMA_EXPORT/USER ORA-31684: Object type USER:"SCOTT" already exists Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE ORA-39120:Table "SCOTT"."DEPT" can't be truncated,data will beskipped. Failing error is: ORA-02266:unique/primary keys in table referenced by enabled foreign keys ORA-00955:name is already used by an existing object Table"SCOTT"."SALGRADE" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped dueto table_exists_action of truncate Table"SCOTT"."BONUS" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped dueto table_exists_action of truncate Table"SCOTT"."EMP" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped dueto table_exists_action of truncate Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA ORA-31693: Table data object "SCOTT"."EMP"failed to load/unload and is being skipped due to error: ORA-02291: integrity constraint (SCOTT.FK_DEPTNO) violated - parentkey not found . . imported "SCOTT"."SALGRADE" 5.859 KB 5 rows . . imported "SCOTT"."BONUS" 0 KB 0 rows Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX ORA-39112: Dependent object typeINDEX:"SCOTT"."PK_DEPT" skipped,base object typeTABLE:"SCOTT"."DEPT" creation failed Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT ORA-39112: Dependent object typeCONSTRAINT:"SCOTT"."PK_DEPT" skipped,base object typeTABLE:"SCOTT"."DEPT" creation failed Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completedwith 5 error(s) at Mon Apr 24 16:18:00 2017 elapsed 0 00:00: 注意：如果这里选用append选项，那么如果原表有数据，且没有合理的约束条件，则可能导致数据的重复导入，所以，在生产环境实际导入过程中一定要弄清楚数据的实际情况才能准确决定如何选用此参数的选项。 2.6 并行导入scott用户下所有的内容；
[oracle@seiang ~]$ impdp system directory=imp_wjq schemas=scott table_exists_action=replacedumpfile=scott_all%U.dmplogfile=imp_scott_all_U.log parallel=2 Import: Release 12.2.0.1.0 - Production on Mon Apr 24 16:26:42 2017 Copyright (c) 1982,Oracle and/or its affiliates. All rights reserved. Password: Connected to: Oracle Database 12c Enterprise Edition Release12.2.0.1.0 - 64bit Production Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01"successfully loaded/unloaded Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/******** directory=imp_wjqschemas=scott table_exists_action=replace dumpfile=scott_all%U.dmplogfile=imp_scott_all_U.log parallel=2 Processing object type SCHEMA_EXPORT/USER ORA-31684:Object type USER:"SCOTT" already exists Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA . . imported "SCOTT"."DEPT" 5.929 KB 4 rows . . imported "SCOTT"."EMP" 8.562 KB 14 rows . . imported "SCOTT"."SALGRADE" 5.859 KB 5 rows . . imported "SCOTT"."BONUS" 0 KB 0 rows Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completedwith 1 error(s) at Mon Apr 24 16:26:52 2017 elapsed 0 00:00:06 3. 特殊需求特殊需求环境准备：
3.1 如果导入环境的用户不同；需求：将原scott用户的数据导入到现在的scott2用户。 [oracle@seiang orcl]$ impdp system directory=imp_wjq schemas=scott remap_schema=scott:scott2table_exists_action=replacedumpfile=scott_all%U.dmp logfile=imp_scott2_all.log parallel=2 Import: Release 12.2.0.1.0 - Production on Mon Apr 24 16:46:13 2017 Copyright (c) 1982,Oracle and/or its affiliates. All rights reserved. Password: Connected to: Oracle Database 12c Enterprise Edition Release12.2.0.1.0 - 64bit Production Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01"successfully loaded/unloaded Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/******** directory=imp_wjqschemas=scott remap_schema=scott:scott2 table_exists_action=replacedumpfile=scott_all%U.dmp logfile=imp_scott2_all.log parallel=2 Processing object type SCHEMA_EXPORT/USER ORA-31684: Object type USER:"SCOTT2" already exists Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA . . imported "SCOTT2"."DEPT" 5.929 KB 4 rows . . imported "SCOTT2"."EMP" 8.562 KB 14 rows . . imported "SCOTT2"."SALGRADE" 5.859 KB 5 rows . . imported "SCOTT2"."BONUS" 0 KB 0 rows Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completedwith 1 error(s) at Mon Apr 24 16:46:24 2017 elapsed 0 00:00:06 3.2 如果导入环境的表空间也不同；需求：将原users表空间的对象重定向到users2表空间。 [oracle@seiang orcl]$ impdp system directory=imp_wjq schemas=scottremap_schema=scott:scott2 remap_tablespace=users:user2table_exists_action=replace dumpfile=scott_all%U.dmp logfile=imp_scott2_all.logparallel=2 Import: Release 12.2.0.1.0 - Production on Mon Apr 24 16:47:59 2017 Copyright (c) 1982,Oracle and/or its affiliates. All rights reserved. Password: Connected to: Oracle Database 12c Enterprise Edition Release12.2.0.1.0 - 64bit Production Master table "SYSTEM"."SYS_IMPORT_SCHEMA_01"successfully loaded/unloaded Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/******** directory=imp_wjqschemas=scott remap_schema=scott:scott2 remap_tablespace=users:user2table_exists_action=replace dumpfile=scott_all%U.dmp logfile=imp_scott2_all.logparallel=2 Processing object type SCHEMA_EXPORT/USER ORA-31684: Object type USER:"SCOTT2" already exists Processing object type SCHEMA_EXPORT/SYSTEM_GRANT Processing object type SCHEMA_EXPORT/ROLE_GRANT Processing object type SCHEMA_EXPORT/DEFAULT_ROLE Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA Processing object type SCHEMA_EXPORT/TABLE/TABLE Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA . . imported "SCOTT2"."DEPT" 5.929 KB 4 rows . . imported "SCOTT2"."EMP" 8.562 KB 14 rows . . imported "SCOTT2"."SALGRADE" 5.859 KB 5 rows . . imported "SCOTT2"."BONUS" 0 KB 0 rows Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completedwith 1 error(s) at Mon Apr 24 16:48:10 2017 elapsed 0 00:00:06 根据结果，可以发现导入的日志最后都提示有一个错误，往上查发现是报错ORA-31684用户已存在，这是因为我们习惯在导入前建立好对应的用户，避免一些其他的权限错误，所以这个错误是可以忽略的。当然其实如果我们已经建立了对应的表空间，用户也是可以不事先建立的，在导入的时候，如果用户不存在，会自动创建用户；
|
