Shulou(Shulou.com)06/01 Report--

Oracle uses data pump (expdp/impdp) to implement migration

Experimental environment:

1. Export environment: RedHat6.4+Oracle 11.2.0.4.0, using the scott sample users included in the database to carry out experimental tests.

Directory:wjq à / tmp/seiang_wjq

2. Import environment: Centos7.1+Oracle 12.2.0.1.0 Oracle12c does not have scott users by default

Directory:imp_wjq à / tmp/imp_comsys

1. Export data:

Special note: if the subsequent database version to be imported is low, all export commands need to be followed by a version=-specified version. For example, 11g-> 10g, suppose the specific version of 10g is 10.2.0.1, then add a version of the parameter version=10.2.0.1.

1. First you need to create a Directory

Note: directories need to be real on the system (mkdir / tmp/seiang_wjq) and have access permissions.

two。 Export user data using expdp

2.1 only export metadata for scott users and does not contain statistics

[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 Export data for scott users only

[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 Export only emp,dept tables and data under scott users

[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

If you use the scott user to export here, you need to pay attention to the permission of the scott user to the directory: the dba user is required to give the scott user the permission to the read,write directory.

[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, 2011, 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, OLAP, 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.4Exporting emp,dept table structure under scott users only

[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, 2011, 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, OLAP, Data Mining and Real ApplicationTesting options

Starting "SCOTT". "SYS_EXPORT_TABLE_01": scott/* directory=wjq tables=emp,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 Export all content under scott users

[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, 2011, 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, OLAP, 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 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" successfullycompleted at Mon Apr 24 14:38:30 2017 elapsed 0 00:00:16

2.6.Exporting all content under scott users in parallel

[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, 2011, 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, OLAP, 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. Query the table space used by the current user

Second, import data

Import preparation: send the content just exported from 11g database to 12c via scp

1. First you need to create a Directory

two。 Import user data using impdp 2.1 Import metadata for scott users without statistical information

[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 Import data for scott users

You can import data only after 2.1 has imported metadata.

[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, 2017, 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 Import only emp tables and data under scott users

[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, 2017, 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

Since the imp_wjq directory is not given read and write permissions during import, the above error occurs. Here is the authorization for the imp_wjq directory:

[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, 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=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 with2 error (s) at Mon Apr 24 15:45:13 2017 elapsed 0 00:00:04

Only import the emp,dept table structure under scott users.

Due to the execution of the previous imports of 2.1,2.2,2.3, all the tables have been imported successfully. For the following experiment, we delete all the tables that exist under scott users.

[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, 2017, 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

Since the export is the two tables of emp,dept, it is possible not to specify tables. The following two ways of writing are allowed here:

[oracle@seiang ~] $impdp scott directory=imp_wjqdumpfile=scott_emp_dept_meta.dmp logfile=imp_scott_emp_dept_meta.log

Or

[oracle@seiang ~] $impdp scott directory=imp_wjqdumpfile=scott_emp_dept_meta.dmp logfile=imp_scott_emp_dept_meta.log full=y

2.5 Import all content under scott users

If you import directly on the basis of 2.4, the import of emp,dept data will be skipped because the default option of table_exists_action parameter is skip, because emp,dept table already exists, as shown below:

[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, 2017, 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

So if we want to import this data, we can add the parameter table_exists_action and specify the options we want.

TABLE_EXISTS_ACTION

Action to take if imported object already exists.

Valid keywords are: APPEND, REPLACE, [SKIP] and TRUNCATE.

Truncate is selected here, that is, if the table exists, the way to handle it is to truncate the data contained in the import file after the table.

[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, 2017, 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:

Note: if the append option is selected here, then if the original table has data and there are no reasonable constraints, it may lead to repeated import of the data. Therefore, in the actual import process in the production environment, you must find out the actual situation of the data in order to accurately determine how to select the option of this parameter.

2.6 parallel import of all content under scott users

[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, 2017, 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. Special needs

Special requirements environment preparation:

(1) create tablespace user2:

SYS@ORCL > create tablespace user2 datafile'/ u01/app/oracle/oradata/orcl/user02.dbf'size 20m autoextend on maxsize 5G

(2) create a user scott2:

SYS@ORCL > create user scott2 identified by tiger defaulttablespace user2

(3) authorized user scott2:

SYS@ORCL > grant connect, resource to scott2

3.1 if the users of the import environment are different

Requirements: import the data of the original scott user into the current scott2 user.

[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, 2017, 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 if the tablespace of the import environment is also different

Requirements: redirect objects from the original users table space to the users2 table space.

[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, 2017, 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

According to the results, it can be found that the imported log finally indicates an error. Looking up, it is found that the error ORA-31684 user already exists. This is because we are used to establishing the corresponding user before import to avoid some other permission errors, so this error can be ignored. Of course, if we have already established the corresponding tablespace, the user can also be created without prior establishment. At the time of import, if the user does not exist, the user will be created automatically.

Author: SEian.G (hard practice changes in 72, but it is difficult to laugh at 81)

◇ article is available at ITPUB: http://blog.itpub.net/31015730/

51CTO: synchronous updates on http://seiang.blog.51cto.com/

◇ article itpub address: http://blog.itpub.net/31015730/viewspace-2137909/

◇ article 51CTO address: http://seiang.blog.51cto.com/10819863/1919003

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.