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.


  1. SKIP: Default value for this parameter is SKIP. This parameter is exactly same as the IGNORE=Y option in conventional import utility.
  2. 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.
  3. TRUNCATE: This option truncate the exiting rows in the table and insert the rows from the dump
  4. 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.
See some examples here.

This is my sample table employee
SQL> select * from employee;

EMP_NAME                             DEPT        SAL
------------------------------ ---------- ----------
Rupal                                  10       5000
Hero                                   10       5500
Jain                                   10       4000
John                                   20       6000
I took the data pump dump for employee table.
$ expdp directory=exp_dir tables=scott.employee dumpfile=emp.dmp logfile=emp.log

Export: Release 11.2.0.2.0 - Production on Tue May 1 23:31:04 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Starting "SYS"."SYS_EXPORT_TABLE_01":  /******** AS SYSDBA directory=exp_dir tables=scott.employee dumpfile=emp.dmp logfile=emp.log
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
. . exported "SCOTT"."EMPLOYEE"                        5.953 KB       4 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
  /home/oracle/shony/emp.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at 23:31:20
Now Let’s try each options.
  1. TABLE_EXISTS_ACTION=SKIP

$ impdp directory=exp_dir dumpfile=emp.dmp logfile=imp1.log table_exists_action=skip

Import: Release 11.2.0.2.0 - Production on Tue May 1 23:36:07 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":  /******** AS SYSDBA directory=exp_dir dumpfile=emp.dmp logfile=imp1.log table_exists_action=skip
Processing object type TABLE_EXPORT/TABLE/TABLE
Table "SCOTT"."EMPLOYEE" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at 23:36:13
  1. 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 employee (select * from emp where dept>20);

4 rows created.

SQL> commit;
SQL> select * from employee;

EMP_NAME                             DEPT        SAL
------------------------------ ---------- ----------
Kiran                                  30       5500
Peter                                  30       6800
King                                   30       7600
Roshan                                 30       5500

$  impdp directory=exp_dir dumpfile=emp.dmp logfile=imp1.log table_exists_action=append

Import: Release 11.2.0.2.0 - Production on Wed May 2 00:50:18 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":  /******** AS SYSDBA directory=exp_dir dumpfile=emp.dmp logfile=imp1.log table_exists_action=append
Processing object type TABLE_EXPORT/TABLE/TABLE
Table "SCOTT"."EMPLOYEE" 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 "SCOTT"."EMPLOYEE"                        5.953 KB       4 rows
Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at 00:50:25


Now 4 more rows appended to the table.
  1* select * from employee
SQL> /

EMP_NAME                             DEPT        SAL
------------------------------ ---------- ----------
Kiran                                  30       5500
Peter                                  30       6800
King                                   30       7600
Roshan                                 30       5500
Rupal                                  10       5000
Hero                                   10       5500
Jain                                   10       4000
John                                   20       6000

8 rows selected.

  1. 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 employee table has 8 rows which we inserted last insert.
$  impdp directory=exp_dir dumpfile=emp.dmp logfile=imp1.log table_exists_action=truncate

Import: Release 11.2.0.2.0 - Production on Wed May 2 00:55:03 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":  /******** AS SYSDBA directory=exp_dir dumpfile=emp.dmp logfile=imp1.log table_exists_action=truncate
Processing object type TABLE_EXPORT/TABLE/TABLE
Table "SCOTT"."EMPLOYEE" 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 "SCOTT"."EMPLOYEE"                        5.953 KB       4 rows
Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at 00:55:09

  1* select * from employee
SQL> /

EMP_NAME                             DEPT        SAL
------------------------------ ---------- ----------
Rupal                                  10       5000
Hero                                   10       5500
Jain                                   10       4000
John                                   20       6000

  1. 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 directory=exp_dir dumpfile=emp.dmp logfile=imp1.log table_exists_action=replace

Import: Release 11.2.0.2.0 - Production on Wed May 2 00:57:35 2012

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Username: / as sysdba

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Master table "SYS"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_FULL_01":  /******** AS SYSDBA directory=exp_dir dumpfile=emp.dmp logfile=imp1.log table_exists_action=replace
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."EMPLOYEE"                        5.953 KB       4 rows
Job "SYS"."SYS_IMPORT_FULL_01" successfully completed at 00:57:40

Now if you check the last_ddl_time for the table it would be the same as the import time.

$ date
Wed May  2 00:58:21 EDT 2012

select OBJECT_NAME, to_char(LAST_DDL_TIME,'dd-mm-yyyy hh:mi:ss') created from dba_objects where OBJECT_NAME='EMPLOYEE'
SQL> /

OBJECT_NAME          CREATED
-------------------- -------------------
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google