I will explain How to Use TABLE_EXISTS_ACTION During IMPDP in Oracle Datapump in this post. Let’s review the TABLE_EXISTS_ACTION in IMPDP in Oracle Datapump.
You can read the following post to learn more detail about Oracle Datapump.
How to Use CONTENT ( DATA_ONLY , METADATA_ONLY ) Parameter in Oracle Datapump
Oracle Data Pump – Export Import ( Expdp Impdp ) Tutorial with Examples-4
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=DATAPUMP LOGFILE=HR.log table_exists_action=REPLACE
Do you want to learn more details about RMAN, then Click this Link and read the articles.