IMPDP TABLE_EXISTS_ACTION PARAMETER EXPLAINED
Data Pump IMPDP TABLE_EXISTS_ACTION = APPEND, REPLACE, [SKIP] and TRUNCATE
In conventional import utility (IMP) we have ignore =y option which will ignore the error when the object is already exist with the same name.
When it comes to the data pump there is one enhanced option of ignore=y which is called TABLE_EXISTS_ACTION. The values for this parameter give 4 different options to handle the existing table and data.
$ impdp help=y
TABLE_EXISTS_ACTION
Action to take if imported object already exists.
Valid keywords are: APPEND, REPLACE, [SKIP] and TRUNCATE.
SKIP: Default value for this parameter is SKIP. This parameter is exactly same as the IGNORE=Y option in conventional import utility.
APPEND: This option appends the data from the data dump. The extra rows in the dump will be appended to the table and the existing data remains unchanged.
TRUNCATE: This option truncate the exiting rows in the table and insert the rows from the dump
REPLACE: This option drop the current table and create the table as it is in the dump file. Both SKIP and REPLACE options are not valid if you set the CONTENT=DATA_ONLY for the impdp.
Method to Import only rows does not exist in the target table
See some examples here.
In this example lets use abc table in my schema (ATOORPU)
SQL> select * from abc;
USERNAME USER_ID CREATED
------------------------------ ---------- ---------
eds 44 19-DEC-12
rew 33 19-DEC-12
ARV 70 20-DEC-12
ARVIND 69 20-DEC-12
ATOORPU 64 19-DEC-12
BI 63 19-DEC-12
I took the data pump dump export EXPDP of employee table.
oracle@orcl: $ expdp atoorpu directory=DPUMP dumpfile=abc.dmp logfile=abc.log tables=abc
Export: Release 11.2.0.1.0 - Production on Fri Oct 24 09:25:02 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "ATOORPU"."SYS_EXPORT_TABLE_01": atoorpu/******** directory=DPUMP dumpfile=abc.dmp logfile=abc.log tables=abc
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "ATOORPU"."ABC" 5.921 KB 6 rows
Master table "ATOORPU"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for ATOORPU.SYS_EXPORT_TABLE_01 is:
/u01/app/oracle/dpump/abc.dmp
Job "ATOORPU"."SYS_EXPORT_TABLE_01" successfully completed at 09:25:36
oracle@qpdbdev201:[/u01/app/oracle/dpump] $ sqlplus atoorpu
SQL*Plus: Release 11.2.0.1.0 Production on Fri Oct 24 09:25:57 2014
Copyright (c) 1982, 2009, Oracle. All rights reserved.
------ TABLE_EXISTS_ACTION=SKIP ------
In this example I want to use table_exists_action=skip, where I want to skip the table data in my import, if a similar table exists.
oracle@orcl: $ impdp atoorpu directory=DPUMP dumpfile=abc.dmp logfile=abc_imp_skip.log tables=abc table_exists_action=skip
Import: Release 11.2.0.1.0 - Production on Fri Oct 24 09:32:32 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "ATOORPU"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "ATOORPU"."SYS_IMPORT_TABLE_01": atoorpu/******** directory=DPUMP dumpfile=abc.dmp logfile=abc_imp_skip.log tables=abc table_exists_action=skip
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39151: Table "ATOORPU"."ABC" 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/STATISTICS/TABLE_STATISTICS
Job "ATOORPU"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 09:32:42
------ TABLE_EXISTS_ACTION=APPEND ------
I have deleted and inserted 4 new rows into employee table. So as of now the rows the dump and table are different and I am going to import the dump with APPEND option.
SQL> delete from employee;
4 rows deleted.
SQL> insert into abc (select * from abc_bak);
4 rows created.
SQL> commit;
SQL> select * from abc;
USERNAME USER_ID CREATED
------------------------------ ---------- ---------
ARVd 70 20-DEC-12
ARVIND2 69 20-DEC-12
ATOORPUe 64 19-DEC-12
BIf 63 19-DEC-12
$ impdp atoorpu directory=DPUMP dumpfile=abc.dmp logfile=abc_imp_append.log tables=abc table_exists_action=append
Import: Release 11.2.0.1.0 - Production on Fri Oct 24 09:37:34 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "ATOORPU"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "ATOORPU"."SYS_IMPORT_TABLE_01": atoorpu/******** directory=DPUMP dumpfile=abc.dmp logfile=abc_imp_append.log tables=abc table_exists_action=append
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39152: Table "ATOORPU"."ABC" 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 TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "ATOORPU"."ABC" 5.921 KB 6 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "ATOORPU"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 09:37:42
Now 4 more rows appended to the table. Lets verify that
SQL> select * from abc;
USERNAME USER_ID CREATED
------------------------------ ---------- ---------
ARVd 70 20-DEC-12
ARVIND2 69 20-DEC-12
ATOORPUe 64 19-DEC-12
BIf 63 19-DEC-12
eds 44 19-DEC-12
rew 33 19-DEC-12
ARV 70 20-DEC-12
ARVIND 69 20-DEC-12
ATOORPU 64 19-DEC-12
BI 63 19-DEC-12
8 rows selected.
------ TABLE_EXISTS_ACTION=TRUNCATE ------
Now let’s try with table_exists_action=truncate option. In truncate option it will truncate the content of the existing table and insert the rows from the dump. Currently my abc table has 8 rows which we inserted last insert.
$ impdp atoorpu directory=DPUMP dumpfile=abc.dmp logfile=abc_imp_trunc.log tables=abc table_exists_action=truncate
Import: Release 11.2.0.1.0 - Production on Fri Oct 24 09:39:39 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "ATOORPU"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "ATOORPU"."SYS_IMPORT_TABLE_01": atoorpu/******** directory=DPUMP dumpfile=abc.dmp logfile=abc_imp_trunc.log tables=abc table_exists_action=truncate
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-39153: Table "ATOORPU"."ABC" 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 TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "ATOORPU"."ABC" 5.921 KB 6 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "ATOORPU"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 09:39:46
SQL > select * from abc
USERNAME USER_ID CREATED
------------------------------ ---------- ---------
eds 44 19-DEC-12
rew 33 19-DEC-12
ARV 70 20-DEC-12
ARVIND 69 20-DEC-12
ATOORPU 64 19-DEC-12
BI 63 19-DEC-12
------ TABLE_EXISTS_ACTION=REPLACE ------
This option drop the current table in the database and the import recreate the new table as in the dumpfile.
$ impdp atoorpu directory=DPUMP dumpfile=abc.dmp logfile=abc_imp_replace.log tables=abc table_exists_action=replace
Import: Release 11.2.0.1.0 - Production on Fri Oct 24 09:41:59 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "ATOORPU"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "ATOORPU"."SYS_IMPORT_TABLE_01": atoorpu/******** directory=DPUMP dumpfile=abc.dmp logfile=abc_imp_replace.log tables=abc table_exists_action=replace
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "ATOORPU"."ABC" 5.921 KB 6 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "ATOORPU"."SYS_IMPORT_TABLE_01" successfully completed at 09:42:07
Now if you check the last_ddl_time for the table it would be the same as the import time.
SQL> select OBJECT_NAME, to_char(LAST_DDL_TIME,'dd-mm-yyyy hh:mi:ss') created from dba_objects where OBJECT_NAME='ABC' and owner='ATOORPU';
OBJECT_NAME CREATED
-------------------------- -------------------
ABC 24-10-2014 09:42:06
Thank you for your article. It was helpful in showing what happens for importing for the different parameters of TABLE_EXISTS_ACTION. :) I saw that for each of the options (Skip, Truncate, Append, Replace), all except REPLACE ignored the metadata.
ReplyDeleteQuestion: If I do an impdp with CONTENT=METADATA_ONLY, how does that work if I specify also TABLE_EXISTS_ACTION=REPLACE ?
Will all data in the destination DB be dropped? (because no new data is coming in from the import, so the data is "replaced" with emptiness)
Context: I am trying to import the metadata of a database into another database. The databases have a similar structure already, but there may be some differences in the table definitions (an extra column, additional constraint, etc). I would like to import the metadata differences, but keep the table rows in the destination DB, if possible.
I have had previous experience of trying to import a db, and ran into the issue of it skipping tables (because that was the default behaviour), and none of the new metadata information that I wanted was imported... So I suspected that I need to specify another behaviour for the TABLE_EXISTS_ACTION.