I will explain How to Use TABLE_EXISTS_ACTION During IMPDP in Oracle Datapump in this post.
Table_exists_action: While Import process is running if any table exists in database what will Oracle do ? replace existing table or skip if it exists. this parameter can take following values.
TABLE_EXISTS_ACTION=[SKIP | APPEND | TRUNCATE | REPLACE]
SKIP is default value. If SKIP is used then table replacing is not done.
APPEND loads rows from the export files and leaves target existing rows unchanged.
TRUNCATE deletes existing rows in target table and then loads rows from the export.
REPLACE drops the existing table in the target and then creates and loads it from the export.
For example: you run the following import command, If you don’t use table_exists_action parameter or use table_exists_action=skip, then Oracle will not perform table replacing when same tables exist in the same schema. And the old tables and their data remain in the Original table.
the new table data= Old data and Old Metadata impdp \”/ as sysdba\” SCHEMAS=HR DIRECTORY=DATAPUMP LOGFILE=HR.log impdp \”/ as sysdba\” SCHEMAS=HR DIRECTORY=DATAPUMP LOGFILE=HR.log table_exists_action=skip
If you use table_exists_action=APPEND option, then Oracle will load rows from the export files and leaves target existing rows unchanged. Probably lots of data will be duplicated.
the new table data= Old data + Export data impdp \”/ as sysdba\” SCHEMAS=HR DIRECTORY=DATAPUMP LOGFILE=HR.log table_exists_action=APPEND
If you use table_exists_action=TRUNCATE, then Oracle will deletes ( truncate table ) existing rows in target table and then loads rows from the export.
the new table data= Only Export data impdp \”/ as sysdba\” SCHEMAS=HR DIRECTORY=DATAPUMP LOGFILE=HR.log table_exists_action=TRUNCATE
If you use table_exists_action=REPLACE , then Oracle will drop the existing table in the target and then creates and loads it from the export.
the new table data= Export data and Export Metadata impdp \”/ as sysdba\” SCHEMAS=HR DIRECTORY= LOGFILE=HR.log table_exists_action=REPLACE
