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.
This is my sample table employee
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 for employee table.
[email protected]: $ 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
[email protected]:[/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 ——
[email protected]: $ 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.
1* select * from abc
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
1* select * from abc
SQL> /
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.
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’;
SQL> /
OBJECT_NAME CREATED
ABC 24-10-2014 09:42:06
