Blog

Syntax :

 TABLE_EXISTS_ACTION=[SKIP | APPEND | TRUNCATE | REPLACE]

The possible values have the following effects:

  • SKIP leaves the table as is and moves on to the next object. This is not a valid option if the CONTENT parameter is set to DATA_ONLY.
  • APPEND loads rows from the source and leaves existing rows unchanged.
  • TRUNCATE deletes existing rows and then loads rows from the source.
  • REPLACE drops the existing table and then creates and loads it from the source. This is not a valid option if the CONTENT parameter is set to DATA_ONLY.

TABLE_EXISTS_ACTION=SKIP

This is the defult option with impdp. I.e if the the table exists, it will skip that table.

[oracle@test ~]$ impdp \'sys/Oracle1234@pdbtest as sysdba\' tables=testuser.abc directory=dir dumpfile=dir1.dmp logfile=testtable1.log table_exists_action=SKIP
Import: Release 19.0.0.0.0 - Production on Sat Jul 16 14:16:02 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_01":  "sys/****@pdbtest AS SYSDBA" tables=testuser.abc directory=dir dumpfile=dir1.dmp logfile=testtable1.log table_exists_action=SKIP
Processing object type TABLE_EXPORT/TABLE/TABLE
Table "TESTUSER"."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
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at Sat Jul 16 14:16:03 2022 elapsed 0 00:00:01
[oracle@test ~]$

TABLE_EXISTS_ACTION=APPEND:   With this option, while importing the table, if the table exists in the database, then it will append the data on top the existing data in the table.

[oracle@test ~]$
[oracle@test ~]$ impdp \'sys/Oracle1234@pdbtest as sysdba\' tables=testuser.abc directory=dir dumpfile=dir1.dmp logfile=testtable1.log table_exists_action=APPEND

Import: Release 19.0.0.0.0 - Production on Sat Jul 16 14:13:58 2022
Version 19.3.0.0.0

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

Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_01":  "sys/****@pdbtest AS SYSDBA" tables=testuser.abc directory=dir dumpfile=dir1.dmp logfile=testtable1.log table_exists_action=APPEND
Processing object type TABLE_EXPORT/TABLE/TABLE
Table "TESTUSER"."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 "TESTUSER"."ABC"                            5.585 KB       6 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at Sat Jul 16 14:14:00 2022 elapsed 0 00:00:02
[oracle@test ~]$

TABLE_EXISTS_ACTION=TRUNCATE:

While importing the table, if the table exists in database, it will truncate the table and load the data.

[oracle@test ~]$ impdp \'sys/Oracle1234@pdbtest as sysdba\' tables=testuser.abc directory=dir dumpfile=dir1.dmp logfile=testtable1.log table_exists_action=TRUNCATE
Import: Release 19.0.0.0.0 - Production on Sat Jul 16 14:15:12 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_01":  "sys/****@pdbtest AS SYSDBA" tables=testuser.abc directory=dir dumpfile=dir1.dmp logfile=testtable1.log table_exists_action=TRUNCATE
Processing object type TABLE_EXPORT/TABLE/TABLE
Table "TESTUSER"."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 "TESTUSER"."ABC"                            5.585 KB       6 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at Sat Jul 16 14:15:13 2022 elapsed 0 00:00:01
[oracle@test ~]$

TABLE_EXISTS_ACTION=REPLACE:

While importing , if the table exists in database, then it will drop it and recreate it from the dump.

[oracle@test ~]$ impdp \'sys/Oracle1234@pdbtest as sysdba\' tables=testuser.abc directory=dir dumpfile=dir1.dmp logfile=testtable1.log table_exists_action=REPLACE
Import: Release 19.0.0.0.0 - Production on Sat Jul 16 14:09:55 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_01":  "sys/****@pdbtest AS SYSDBA" tables=testuser.abc directory=dir dumpfile=dir1.dmp logfile=testtable1.log table_exists_action=REPLACE
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "TESTUSER"."ABC"                            5.585 KB       6 rows
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at Sat Jul 16 14:09:57 2022 elapsed 0 00:00:02
[oracle@test ~]$

Hope It Helps !

Follow me

Contact us for Training/ Job Support

Caution: Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.