Use of TABLE_EXISTS_ACTION option with IMPDP

Replace the tables with IMPDP datapump which already present in schema

TABLE_EXISTS_ACTION: Action to take if imported object already exists.
Valid values are: APPEND, REPLACE, SKIP and TRUNCATE.
TABLE_EXISTS_ACTION=SKIP:
Default option with IMPDP. If the the table exists, it will skip that table.
TABLE_EXISTS_ACTION=APPEND:
while IMPDP, if the table exists in the database, then it will append the data on top the existing data in the table.
impdp dumpfile=emp.dmp logfile=emp.log directory=dbbackup table_exists_action=APPEND
TABLE_EXISTS_ACTION=TRUNCATE:
While IMPDP, if the table exists in database, it will truncate the table and load the data.
impdp dumpfile=emp.dmp logfile=emp.log directory=dbbackup table_exists_action=TRUNCATE
TABLE_EXISTS_ACTION=REPLACE:
While importing, if the table exists in database, then it will drop it and recreate it from the dump
impdp dumpfile=emp.dmp logfile=emp.log directory=dbbackup table_exists_action=REPLACE

Example of replacing the existing table if already exists with REPLACE value in IMPDP process
1. Create a directory dbbackup1 where your dump file is placed for import operation.

create directory dbbackup as 'D:\dumpfile';

2. Grant read, write permission on directory to scott user:

grant read, write on directory dbbackup to Scott;

3. Import the table which is already exists in the database with the replace option of TABLE_EXISTS_ACTION parameter otherwise import process will generate the error that table is already exists in the database.

impdp directory=dbbackup dumpfile=SCOTT_DUMP.DMP logfile=SCOTT_DUMP_IMP.log
tables=SCOTT.INVOICE_GENERATION_TIME, SCOTT.POS_GROUP_IDS
TABLE_EXISTS_ACTION=replace

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.