Data Pump Parameter-Table_exists_action

Data Pump Parameter-Table_exists_action

When importing a table, Data Pump skips the job if the object already exists in the imported schema. However, by using the table_exists_action parameter with its available values, it is possible to bypass skipping the table import. This parameter accepts the following values:

  • skip:  Is the default value and used to skip importing the table if it already exists
  • append:  Appends the data to an already existing table
  • truncate:  Truncates the already existing table and imports fresh data
  • replace:  Drops already an existing table and creates a new one with fresh data
Step:1
Create table
[oracle@oracle11g ~]$ sqlplus test/test
SQL*Plus: Release 11.2.0.3.0 Production on Sat Sep 5 16:56:50 2015
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create table test(no number);
Table created.
SQL> insert into test values(1);
1 row created.
SQL> commit;
Commit complete.
Step:2
Export the table
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@oracle11g ~]$ expdp test/test tables=test  directory=test_dir dumpfile=test.dmp logfile=test.log
Export: Release 11.2.0.3.0 – Production on Sat Sep 5 16:57:55 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting “TEST”.”SYS_EXPORT_TABLE_01″:  test/******** tables=test directory=test_dir dumpfile=test.dmp logfile=test.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 “TEST”.”TEST”                               5.007 KB       1 rows
Master table “TEST”.”SYS_EXPORT_TABLE_01″ successfully loaded/unloaded
******************************************************************************
Dump file set for TEST.SYS_EXPORT_TABLE_01 is:
  /u01/dump/test.dmp
Job “TEST”.”SYS_EXPORT_TABLE_01″ successfully completed at 16:58:09
Step:3
using table_exists_action=skip
[oracle@oracle11g ~]$ impdp  test/test tables=test  directory=test_dir dumpfile=test.dmp table_exists_action=skip
Import: Release 11.2.0.3.0 – Production on Sat Sep 5 16:59:02 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table “TEST”.”SYS_IMPORT_TABLE_01″ successfully loaded/unloaded
Starting “TEST”.”SYS_IMPORT_TABLE_01″:  test/******** tables=test directory=test_dir dumpfile=test.dmp table_exists_action=skip
Processing object type TABLE_EXPORT/TABLE/TABLE
Table “TEST”.”TEST” 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 “TEST”.”SYS_IMPORT_TABLE_01″ successfully completed at 16:59:04
Step:4
Using the append value:
[oracle@oracle11g ~]$ impdp  test/test tables=test  directory=test_dir dumpfile=test.dmp table_exists_action=append
Import: Release 11.2.0.3.0 – Production on Sat Sep 5 17:02:22 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table “TEST”.”SYS_IMPORT_TABLE_01″ successfully loaded/unloaded
Starting “TEST”.”SYS_IMPORT_TABLE_01″:  test/******** tables=test directory=test_dir dumpfile=test.dmp table_exists_action=append
Processing object type TABLE_EXPORT/TABLE/TABLE
Table “TEST”.”TEST” 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 “TEST”.”TEST”                               5.007 KB       1 rows
Job “TEST”.”SYS_IMPORT_TABLE_01″ successfully completed at 17:02:27
[oracle@oracle11g ~]$ sqlplus test/test
SQL*Plus: Release 11.2.0.3.0 Production on Sat Sep 5 17:02:39 2015
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select * from test;
        NO
———-
         1
         1
Step:5
Using truncate value
[oracle@oracle11g ~]$ impdp  test/test tables=test  directory=test_dir dumpfile=test.dmp table_exists_action=truncate
Import: Release 11.2.0.3.0 – Production on Sat Sep 5 17:04:35 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table “TEST”.”SYS_IMPORT_TABLE_01″ successfully loaded/unloaded
Starting “TEST”.”SYS_IMPORT_TABLE_01″:  test/******** tables=test directory=test_dir dumpfile=test.dmp table_exists_action=truncate
Processing object type TABLE_EXPORT/TABLE/TABLE
Table “TEST”.”TEST” 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 “TEST”.”TEST”                               5.007 KB       1 rows
Job “TEST”.”SYS_IMPORT_TABLE_01″ successfully completed at 17:04:38
[oracle@oracle11g ~]$ sqlplus test/test
SQL*Plus: Release 11.2.0.3.0 Production on Sat Sep 5 17:04:44 2015
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select * from test;
        NO
———-
         1
Step:6
Using Replace value
[oracle@oracle11g ~]$ sqlplus test/test
SQL*Plus: Release 11.2.0.3.0 Production on Sat Sep 5 17:04:44 2015
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select * from test;
        NO
———-
         1
SQL> update test set no=2 where no=1;
1 row updated.
SQL> commit;
Commit complete.
SQL> select * from test;
        NO
———-
         2
[oracle@oracle11g ~]$ impdp  test/test tables=test  directory=test_dir dumpfile=test.dmp table_exists_action=replace
Import: Release 11.2.0.3.0 – Production on Sat Sep 5 17:07:33 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table “TEST”.”SYS_IMPORT_TABLE_01″ successfully loaded/unloaded
Starting “TEST”.”SYS_IMPORT_TABLE_01″:  test/******** tables=test directory=test_dir dumpfile=test.dmp table_exists_action=replace
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported “TEST”.”TEST”                               5.007 KB       1 rows
Job “TEST”.”SYS_IMPORT_TABLE_01″ successfully completed at 17:07:36
[oracle@oracle11g ~]$ sqlplus test/test
SQL*Plus: Release 11.2.0.3.0 Production on Sat Sep 5 17:09:49 2015
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select * from test;
        NO
———-
         1
  • September 5, 2015 | 13 views
  • Comments