[IMPDP] how to deal with the problem of table storage when importing data -- TABLE_EXISTS_ACTION Parameter

[Transferred from: http://space.itpub.net/519536/viewspace-631445]

When impdp is used to import a database, if a table already exists, Oracle provides the following four processing methods:
A. Ignore (Skip, default Act );
B. Add (append) based on the original data );
C. Drop the table first, create the table, and insert data (replace );
D. truncate first, and then complete data insertion (truncate ).

Feel the effects of these methods for your reference.

1. Description of the table_exists_action parameter in impdp online help
Table_exists_action action to take if imported object already exists.
Valid keywords: (skip), append, replace and truncate.

2. description in the Oracle Official Document

Http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/dp_import.htm#sthref365

Oracle official documents are fundamental and need to be studied.

3. Use expdp to generate a dump file
SEC @ secdb/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. log

Export: Release 10.2.0.3.0-64bit production on Thursday, 01 10000l, 2010 10:29:17

Copyright (c) 2003,200 5, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0-64bit Production
With the partitioning, Oracle Label Security, OLAP and Data Mining scoring engine options
Starting "Sec". "sys_export_schema_01": sec/******** directory = expdp_dir dumpfile = 20100401102917_sec.dmp logfile = 20100401102917_sec.log
Estimate in progress using blocks method...
Processing object type schema_export/table/table_data
Total estimation using blocks method: 3 MB
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
Processing object type SCHEMA_EXPORT/TABLE/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
.. Exported "SEC". "T" 2.259 MB 24360 rows
Master table "SEC". "SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
**************************************** **************************************
Dump file set for SEC. SYS_EXPORT_SCHEMA_01 is:
/Expdp/20100401102917_sec.dmp
Job "SEC". "SYS_EXPORT_SCHEMA_01" successfully completed at 10:29:20

4. Use the 4th method to perceive the specific effect.
The IMPDP statements used are summarized here for reference.
Impdp system/sys directory = expdp_dir dumpfile = 20100401102917_sec.dmp logfile = 20100401102917_sec_impdp.log TABLE_EXISTS_ACTION = SKIP
Impdp system/sys directory = expdp_dir dumpfile = 20100401102917_sec.dmp logfile = 20100401102917_sec_impdp.log TABLE_EXISTS_ACTION = APPEND
Impdp system/sys directory = expdp_dir dumpfile = 20100401102917_sec.dmp logfile = 20100401102917_sec_impdp.log TABLE_EXISTS_ACTION = REPLACE
Impdp system/sys directory = expdp_dir dumpfile = 20100401102917_sec.dmp logfile = 20100401102917_sec_impdp.log TABLE_EXISTS_ACTION = TRUNCATE

1) default behavior: SKIP
Sec @ ora10g> select count (*) from t;

COUNT (*)
----------
24360

SEC @ secdb/expdp $ impdp system/sys directory = expdp_dir dumpfile = 20100401102917_sec.dmp logfile = 20100401102917_sec_impdp.log

Import: Release 10.2.0.3.0-64bit production on Thursday, 01 10000l, 2010 10:50:41

Copyright (c) 2003,200 5, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0-64bit Production
With the partitioning, Oracle Label Security, OLAP and Data Mining scoring engine options
Master table "system". "sys_import_full_01" successfully Loaded/unloaded
Starting "system". "sys_import_full_01": System/******** directory = expdp_dir dumpfile = 20100401102917_sec.dmp logfile = 20100401102917_sec_impdp.log
Processing object type schema_export/user
ORA-31684: Object Type User: "Sec" 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
ORA-39151: Table "SEC". "T" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Job "SYSTEM". "SYS_IMPORT_FULL_01" completed with 2 error (s) at 10:50:42

The above behavior is the same as the specified "SKIP" shown in the following one.
Sec @ secDB/expdp $ impdp system/sys directory = expdp_dir dumpfile = 20100401102917_sec.dmp logfile = 20100401102917_sec_impdp.log TABLE_EXISTS_ACTION = SKIP

2) APPEND Mode
Sec @ secDB/expdp $ impdp system/sys directory = expdp_dir dumpfile = 20100401102917_sec.dmp logfile = 20100401102917_sec_impdp.log TABLE_EXISTS_ACTION = APPEND

Import: Release 10.2.0.3.0-64bit production on Thursday, 01 10000l, 2010 10:51:32

Copyright (c) 2003,200 5, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0-64bit Production
With the partitioning, Oracle Label Security, OLAP and Data Mining scoring engine options
Master table "system". "sys_import_full_01" successfully Loaded/unloaded
Starting "system". "sys_import_full_01": System/********* directory = expdp_dir dumpfile = 20100401102917_sec.dmp logfile = paitable_exists_action = append
Processing object type schema_export/user
ORA-31684: Object Type User: "Sec" 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
ORA-39152: Table "Sec". "T" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Processing object type schema_export/table/table_data
.. Imported "Sec". "T" 2.259 MB 24360 rows
Job "system". "sys_import_full_01" completed with 2 error (s) at 10:51:33

In this case, the number of records in table t doubles.
SEC @ ora10g> select count (*) from T;

Count (*)
----------
48720

3) Replace Mode
Sec @ secDB/expdp $ impdp system/sys directory = expdp_dir dumpfile = 20100401102917_sec.dmp logfile = 20100401102917_sec_impdp.log TABLE_EXISTS_ACTION = REPLACE

Import: Release 10.2.0.3.0-64bit Production on Thursday, 01 10000l, 2010 10:54:28

Copyright (c) 2003,200 5, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0-64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Master table "SYSTEM". "SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM". "SYS_IMPORT_FULL_01": system/********* directory = expdp_dir dumpfile = 20100401102917_sec.dmp logfile = paitable_exists_action = REPLACE
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER: "SEC" 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
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
.. Imported "SEC". "T" 2.259 MB 24360 rows
Job "SYSTEM". "SYS_IMPORT_FULL_01" completed with 1 error (s) at 10:54:29

Sec @ ora10g> select count (*) from t;

COUNT (*)
----------
24360

4) TRUNCATE Mode
SEC @ secdb/expdp $ impdp system/sys directory = expdp_dir dumpfile = 20100401102917_sec.dmp logfile = 20100401102917_sec_impdp.log table_exists_action = truncate

Import: Release 10.2.0.3.0-64bit production on Thursday, 01 10000l, 2010 10:54:55

Copyright (c) 2003,200 5, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0-64bit Production
With the partitioning, Oracle Label Security, OLAP and Data Mining scoring engine options
Master table "system". "sys_import_full_01" successfully Loaded/unloaded
Starting "system". "sys_import_full_01": System/********* directory = expdp_dir dumpfile = 20100401102917_sec.dmp logfile = export table_exists_action = truncate
Processing object type schema_export/user
ORA-31684: Object Type User: "Sec" 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
ORA-39153: Table "SEC". "T" 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
.. Imported "SEC". "T" 2.259 MB 24360 rows
Job "SYSTEM". "SYS_IMPORT_FULL_01" completed with 2 error (s) at 10:54:56

Sec @ ora10g> select count (*) from t;

COUNT (*)
----------
24360

5. Summary
Compared with the IMP tool, the TABLE_EXISTS_ACTION parameter of IMPDP is an improvement. Our control measures are enhanced, so we do not have to stick to it.

Good luck.

Secooler
10.04.06

-- The End --