How to Use TABLE_EXISTS_ACTION During IMPDP in Oracle Datapump

Hi,

I will explain How to Use TABLE_EXISTS_ACTION During IMPDP in Oracle Datapump in this post.

 

You can read the following post to learn more detail about Oracle Datapump.

https://ittutorial.org/oracle-data-pump-export-import-4/

How to Use CONTENT ( DATA_ONLY , METADATA_ONLY ) Parameter in Oracle Datapump

 

 

 

 

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 read the following articles.

https://ittutorial.org/rman-backup-restore-and-recovery-tutorials-for-beginners-in-the-oracle-database/

 

 868 views last month,  5 views today

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 mehmetsalih.deveci@outlook.com. -                                                                                                                                                                                                                                                 - Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  mehmetsalih.deveci@outlook.com a mail atabilirsiniz.

Check Also

Alter System Flush Buffer Cache in Oracle

Hi, I will explain Alter System Flush Buffer Cache in Oracle in this post.  3,807 views …

Leave a Reply