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_only exclude=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, 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=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, 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
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.dmp logfile=scott_emp_dept.log
Export: Release 11.2.0.4.0 - Production on Mon Apr 24 14:28:18 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 "SCOTT"."SYS_EXPORT_TABLE_01": scott/******** directory=wjq tables=emp,dept dumpfile=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" successfully completed 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_only dumpfile=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, 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 "SCOTT"."SYS_EXPORT_TABLE_01": scott/******** directory=wjq tables=emp,dept content=metadata_only dumpfile=scott_emp_dept_meta.dmp logfile=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" successfully completed 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.dmp logfile=scott_all.log
Export: Release 11.2.0.4.0 - Production on Mon Apr 24 14:38:10 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 dumpfile=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 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_all.dmp
Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed 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.dmp logfile=scott_all.log parallel=2
Export: Release 11.2.0.4.0 - Production on Mon Apr 24 14:44:04 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 dumpfile=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" successfully completed 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.dmp logfile=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_wjq dumpfile=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" successfully completed 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.dmp logfile=imp_scott_data.log
Import: Release 12.2.0.1.0 - Production on Mon Apr 24 15:29:27 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_wjq dumpfile=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" successfully completed 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=emp dumpfile=scott_emp_dept.dmp logfile=imp_scott_emp.lo
Import: Release 12.2.0.1.0 - Production on Mon Apr 24 15:40:56 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
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.dmp logfile=imp_scott_emp.log
Import: Release 12.2.0.1.0 - Production on Mon Apr 24 15:45:03 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 "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_TABLE_01": scott/******** directory=imp_wjq tables=emp dumpfile=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 data will 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 with 1 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.dmp logfile=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, 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 "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_TABLE_01": scott/******** directory=imp_wjq tables=emp,dept dumpfile=scott_emp_dept_meta.dmp logfile=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" successfully completed at Mon Apr 24 15:59:22 2017 elapsed 0 00:00:02
由於匯出就是emp,dept兩張表，所以也可以不指定tables，以下兩種寫法在這裡都是可以的：
[oracle@seiang ~]$ impdp scott directory=imp_wjq dumpfile=scott_emp_dept_meta.dmp logfile=imp_scott_emp_dept_meta.log
或者
[oracle@seiang ~]$ impdp scott directory=imp_wjq dumpfile=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=scott dumpfile=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, 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_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/******** directory=imp_wjq schemas=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 data will be skipped due to table_exists_action of skip
ORA-39151: Table "SCOTT"."DEPT" exists. All dependent metadata and data 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" completed with 3 error(s) at Mon Apr 24 16:06:38 2017 elapsed 0 00:00:05
所以這時我們想匯入這些資料，可以加引數 table_exists_action，指定想要的選項。TABLE_EXISTS_ACTIONAction to take if imported object already exists.Valid keywords are: APPEND, REPLACE, [SKIP] and TRUNCATE.
這裡選擇truncate，即如果表存在，那麼處理方式是truncate此表後匯入檔案中包含的資料。
[oracle@seiang ~]$ impdp system directory=imp_wjq schemas=scott table_exists_action=truncate dumpfile=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, 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_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/******** directory=imp_wjq schemas=scott table_exists_action=truncate 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-39120: Table "SCOTT"."DEPT" can't be truncated, data will be skipped. 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 due to table_exists_action of truncate
Table "SCOTT"."BONUS" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
Table "SCOTT"."EMP" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to 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 - parent key 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 type INDEX:"SCOTT"."PK_DEPT" skipped, base object type TABLE:"SCOTT"."DEPT" creation failed
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
ORA-39112: Dependent object type CONSTRAINT:"SCOTT"."PK_DEPT" skipped, base object type TABLE:"SCOTT"."DEPT" creation failed
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" completed with 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=replace dumpfile=scott_all%U.dmp logfile=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, 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_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/******** directory=imp_wjq schemas=scott table_exists_action=replace dumpfile=scott_all%U.dmp logfile=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" completed with 1 error(s) at Mon Apr 24 16:26:52 2017 elapsed 0 00:00:06
3. 特殊需求
特殊需求環境準備：
（1）建立表空間user2：SYS@ORCL>create tablespace user2 datafile ''/u01/app/oracle/oradata/orcl/user02.dbf' size 20M autoextend on maxsize 5G;
（2）建立使用者scott2：SYS@ORCL>create user scott2 identified by tiger default tablespace user2;
（3）賦權使用者scott2：SYS@ORCL>grant connect, resource to scott2;
3.1 如果匯入環境的使用者不同；
需求：將原scott使用者的資料匯入到現在的scott2使用者。
[oracle@seiang orcl]$ impdp system directory=imp_wjq schemas=scott remap_schema=scott:scott2 table_exists_action=replace dumpfile=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, 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_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/******** directory=imp_wjq schemas=scott remap_schema=scott:scott2 table_exists_action=replace dumpfile=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" completed with 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=scott remap_schema=scott:scott2 remap_tablespace=users:user2 table_exists_action=replace dumpfile=scott_all%U.dmp logfile=imp_scott2_all.log parallel=2
Import: Release 12.2.0.1.0 - Production on Mon Apr 24 16:47:59 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_SCHEMA_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/******** directory=imp_wjq schemas=scott remap_schema=scott:scott2 remap_tablespace=users:user2 table_exists_action=replace dumpfile=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" completed with 1 error(s) at Mon Apr 24 16:48:10 2017 elapsed 0 00:00:06
根據結果，可以發現匯入的日誌最後都提示有一個錯誤，往上查發現是報錯ORA-31684使用者已存在，這是因為我們習慣在匯入前建立好對應的使用者，避免一些其他的許可權錯誤，所以這個錯誤是可以忽略的。當然其實如果我們已經建立了對應的表空間，使用者也是可以不事先建立的，在匯入的時候，如果使用者不存在，會自動建立使用者；
|
作者：SEian.G（苦練七十二變，笑對八十一難） ◇本文在ITPUB：http://blog.itpub.net/31015730/ 51CTO：http://seiang.blog.51cto.com/上有同步更新 ◇本文itpub地址：http://blog.itpub.net/31015730/viewspace-2137909/ ◇本文51CTO地址：http://seiang.blog.51cto.com/10819863/1919003
|
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31015730/viewspace-2137909/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 使用資料泵（expdp、impdp）遷移資料庫流程資料庫
- 使用impdp,expdp資料泵進入海量資料遷移
- 【Datapump】Oracle資料泵遷移資料命令參考(expdp/impdp說明）Oracle
- 使用expdp、impdp遷移資料庫資料庫
- expdp/impdp使用sysdba許可權遷移資料
- expdp impdp Data Pump（資料泵）使用解析
- EXPDP 和 IMPDP 資料泵的使用_1
- EXPDP 和 IMPDP 資料泵的使用_2
- Oracle資料泵匯出匯入（expdp/impdp）Oracle
- 資料泵基礎(impdp/expdp)
- expdp/impdp 使用version引數跨版本資料遷移
- Oracle使用資料泵expdp,impdp進行資料匯出匯入Oracle
- expdp,impdp的多使用者遷移
- Oracle Expdp/Impdp 進行資料遷移的 幾點注意事項Oracle
- expdp/impdp 遷移表空間
- 按計劃瞭解資料泵expdp/impdp
- 醬油DBA奉獻expdp,impdp多使用者遷移資料（二）
- 一次expdp/impdp遷移案例
- 資料庫泵（expdp/impdp）匯入匯出流程資料庫
- 10g 資料泵(Data Dump) -- EXPDP & IMPDP [zt]
- Oracle資料庫（資料泵）遷移方案（上）Oracle資料庫
- Oracle資料庫（資料泵）遷移方案（下）Oracle資料庫
- oracle資料庫的impdp，expdpOracle資料庫
- 資料庫遷移之資料泵實驗資料庫
- 【BUILD_ORACLE】使用Oracle資料泵線上不停機克隆/遷移PDBUIOracle
- Oracle expdp/impdp 使用示例Oracle
- oracle實驗記錄 （expdp/impdp使用）Oracle
- EXPDP資料泵使用方法
- 使用資料泵impdp匯入資料
- 使用資料泵遷移遇到的問題
- Oracle使用impdp的network_link遷移資料庫Oracle資料庫
- 資料泵datapump（expdp/impdp）的parfile用法，避免跳脫字元字元
- oracle資料泵備份（Expdp命令）Oracle
- 【impdp】IMPDP中的TRANSFORM引數--【資料泵】EXPDP匯出表結構(真實案例)後傳ORM
- oracle expdp和impdp使用例子Oracle
- 【傳輸表空間】使用 EXPDP/IMPDP工具的傳輸表空間完成資料遷移
- Oracle12c中資料泵新特性之功能增強(expdp, impdp)Oracle
- 資料泵 impdp 操作