About Me

My photo
Bangalore, India
I am an Oracle Certified Professional working in SAP Labs. Everything written on my blog has been tested on my local environment, Please test before implementing or running in production. You can contact me at amit.rath0708@gmail.com.

Saturday, August 31, 2013

Table_Exists_Action parameter in IMPDP

Sometimes we need to import data to a table which is existing in our database. If we import it normally then we will get error like existence of table. If we need to preserve the old data and need to append the new data then we have to use TABLE_EXISTS_ACTION parameter of IMPDP utility.

Valid Key words of TABLE_EXISTS_ACTION  are [SKIP/APPEND/TRUNCATE/REPLACE]. Details are:-

1. SKIP :- its the default value , it leaves the table as it is and moves on to the next object. This option is not valid if we want to append the data to existing table. We do not have to externally set this value. 

2. APPEND :- When we want to add data to a existing table then we have to use this parameter. This parameter append data to the table from dump file and leaves the existing data unchanged. This parameter only imports the data in existing table and skip the indexes.

impdp amit/amit directory=datapump dumpfile=expdp_table.dmp remap_schema=amit:test remap_tablespace=amit:test table_exist_action=APPEND &

After importing the table using table_exist_action=APPEND we have to rebuild the indexes.

3. TRUNCATE :- This parameter deletes all the existing data from the table and loads new data to the table from dump file. This parameter skips the metadata of the table.

impdp amit/amit directory=datapump dumpfile=expdp_table.dmp remap_schema=amit:test remap_tablespace=amit:test table_exist_action=TRUNCATE &

4. REPLACE :- This parameter internally drop the table and recreate the table from the dumpfile and loads the new data from dumpfile. Existing metadata of the table also gets dropped and recreated from the dumpfile.

impdp amit/amit directory=datapump dumpfile=expdp_table.dmp remap_schema=amit:test remap_tablespace=amit:test table_exist_action=REPLACE &

If your dumpfile do not have metadata , then using table_exist_action=REPLACE parameter donot import data from dumpfile.

I hope this article helped you.

Regards,
Amit Rath

No comments:

Post a Comment