How to Use TABLE_EXISTS_ACTION During IMPDP in Oracle Datapump

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.

 

 

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply

Your email address will not be published. Required fields are marked *