Table_exists_action Parameter of Data Pump

Table_exists_action Parameter of Data Pump

Sometimes we need to import table into an existing table.If we import the table in that schemas it throws error that table already exist. If we have to reserve the old data of table and append the new data, we can use the table_exists_action parameter of data pump. Some valid keywords are (SKIP | APPEND | TRUNCATE | REPLACE).

The possible values of the following effects are:-

1.) SKIP: - says to ignore the data in the import file and leave the existing table untouched. This is not a valid option if the CONTENT parameter is set to DATA_ONLY.By default the value is SKIP.

2.) APPEND: - loads rows from the source and leaves existing rows unchanged.

3.) TRUNCATE: - truncate the exiting rows in the table and insert the rows from the dump. The truncate option cannot be used over a db link or with a cluster table.

4.) REPLACE: - says to delete the whole table and replace both the table definition and rows from the import dmp file. This is not a valid option if the CONTENT parameter is set to DATA_ONLY.

example :- 

SQL> create user rudra identified by rudra default tablespace mmoney quota unlimited on mmoney;

User created.

SQL> grant create session,resource to rudra;

Grant succeeded.

SQL> create or replace directory abc as '/u01/neeraj';

Directory created.

SQL> grant read,write on directory abc to rudra;

Grant succeeded.

SQL> conn

Enter user-name: rudra

Enter password:

Connected.

SQL> create table r1(id number,name varchar2(20));

Table created.

SQL> insert into r1 values(1,'rudra');

1 row created.

SQL> insert into r1 values(2,'manoj');

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> commit;

Commit complete.

SQL> select * From r1;

        ID NAME

---------- --------------------

         1 rudra

         2 manoj

         2 manoj

         2 manoj

we take a export:- 

[oracle@localhost ~]$ expdp rudra/rudra directory=abc dumpfile=r1.dmp logfile=r1.log schemas=rudra

Export: Release 11.2.0.1.0 - Production on Wed Aug 6 20:59:34 2014

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "RUDRA"."SYS_EXPORT_SCHEMA_01":  rudra/******** directory=abc dumpfile=r1.dmp logfile=r1.log schemas=rudra

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 64 KB

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/COMMENT

. . exported "RUDRA"."R1"      5.453 KB       4 rows

Master table "RUDRA"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

******************************************************************************

Dump file set for RUDRA.SYS_EXPORT_SCHEMA_01 is:

  /u01/neeraj/r1.dmp

Job "RUDRA"."SYS_EXPORT_SCHEMA_01" successfully completed at 21:00:04

[oracle@localhost ~]$

Demo 1:- action_exists_append=skip (by defaults)

[oracle@localhost ~]$ impdp rudra/rudra directory=abc dumpfile=r1.dmp logfile=imp_r1.log schemas=rudra

Import: Release 11.2.0.1.0 - Production on Wed Aug 6 21:02:19 2014

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "RUDRA"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded

Starting "RUDRA"."SYS_IMPORT_SCHEMA_01":  rudra/******** directory=abc dumpfile=r1.dmp logfile=imp_r1.log schemas=rudra

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

ORA-39151: Table "RUDRA"."R1" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Job "RUDRA"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at 21:02:22

Demo 2:- table_exists_action=append  

We have an r1 table and it contains four records.

 [oracle@localhost ~]$ impdp rudra/rudra directory=abc dumpfile=r1.dmp logfile=imp_r1.log schemas=rudra  table_exists_action=append

Import: Release 11.2.0.1.0 - Production on Wed Aug 6 21:06:28 2014

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "RUDRA"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded

Starting "RUDRA"."SYS_IMPORT_SCHEMA_01":  rudra/******** directory=abc dumpfile=r1.dmp logfile=imp_r1.log schemas=rudra table_exists_action=append

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

ORA-39152: Table "RUDRA"."R1" 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 SCHEMA_EXPORT/TABLE/TABLE_DATA

. . imported "RUDRA"."R1"      5.453 KB       4 rows

Job "RUDRA"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at 21:06:30

[oracle@localhost ~]$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Wed Aug 6 21:06:36 2014

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Enter user-name: rudra

Enter password:

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from r1;

        ID NAME

---------- --------------------

         1 rudra

         2 manoj

         2 manoj

         2 manoj

         1 rudra

         2 manoj

         2 manoj

         2 manoj

8 rows selected.

Demo 3:- table_exists_action=truncate

Already we have 8 rows in the r1 table;

[oracle@localhost ~]$ impdp rudra/rudra directory=abc dumpfile=r1.dmp logfile=imp_r1.log schemas=rudra  table_exists_action=truncate

Import: Release 11.2.0.1.0 - Production on Wed Aug 6 21:08:27 2014

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "RUDRA"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded

Starting "RUDRA"."SYS_IMPORT_SCHEMA_01":  rudra/******** directory=abc dumpfile=r1.dmp logfile=imp_r1.log schemas=rudra table_exists_action=truncate

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

ORA-39153: Table "RUDRA"."R1" 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 SCHEMA_EXPORT/TABLE/TABLE_DATA

. . imported "RUDRA"."R1"            5.453 KB       4 rows

Job "RUDRA"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at 21:08:30

[oracle@localhost ~]$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Wed Aug 6 21:08:36 2014

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Enter user-name: rudra

Enter password:

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * From r1;

        ID NAME

---------- --------------------

         1 rudra

         2 manoj

         2 manoj

         2 manoj

Demo 4:- table_exists_action= replace 

Now i have inserted some values in the r1 table.

[oracle@localhost ~]$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Wed Aug 6 21:10:24 2014

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Enter user-name: rudra

Enter password:

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * From r1;

        ID NAME

---------- --------------------

         1 rudra

         2 manoj

         2 manoj

         2 manoj

SQL> insert into r1 values(3,'raj');

1 row created.

SQL> insert into r1 values(4,'neha db');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from r1;

        ID NAME

---------- --------------------

         1 rudra

         2 manoj

         2 manoj

         2 manoj

         3 raj

         4 neha db

6 rows selected.

[oracle@localhost ~]$ impdp rudra/rudra directory=abc dumpfile=r1.dmp logfile=imp_r1.log schemas=rudra  table_exists_action=replace

Import: Release 11.2.0.1.0 - Production on Wed Aug 6 21:14:31 2014

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "RUDRA"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded

Starting "RUDRA"."SYS_IMPORT_SCHEMA_01":  rudra/******** directory=abc dumpfile=r1.dmp logfile=imp_r1.log schemas=rudra table_exists_action=replace

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

. . imported "RUDRA"."R1"    5.453 KB       4 rows

Job "RUDRA"."SYS_IMPORT_SCHEMA_01" successfully completed at 21:14:33

[oracle@localhost ~]$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Wed Aug 6 21:14:37 2014

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Enter user-name: rudra

Enter password:

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from r1;

        ID NAME

---------- --------------------

         1 rudra

         2 manoj

         2 manoj

         2 manoj

 Please share your suggestion and comment on this.

all the best....  :)