DOYENSYS Knowledge Portal




We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal




Saturday, September 5, 2015

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










No comments: