Tuesday, January 10, 2012

data pump TABLE_EXISTS_ACTION parameter


TABLE_EXISTS_ACTION = {SKIP | APPEND | TRUNCATE | REPLACE}
The default value is "SKIP"


NOTE: If  CONTENT=DATA_ONLY  is specified then the default is "APPEND" not SKIP.

The parameter TABLE_EXISTS_ACTION applies only to the Data Pump Import operation. This parameter is used when you import a table which is already exists in import schema. So if you not use this parameter and impdp found that the table which to be imported is already exist then impdp skip this table from import list.

Now you may be interested about rest of the three values-

APPEND - The import will be done if the table does not have any Primary key or Unique key constraints. If such constraint exists then you need to ensure that append operation does not violate Primary key or Unique key constraints (that means it does not occur data duplication).
It Loads Rows from source and leaves existing rows UNCHANGED.

TRUNCATE - If the table is not a parent table ( i.e, No other table references it by creating foreign key constraint) then it truncate the existing data and load data from dump file. Otherwise data will not be loaded.

REPLACE - This is the most tricky value of TABLE_EXISTS_ACTION parameter. If the importing table is a parent table ( i.e, other table references it by creating foreign key constraint ) then the foreign key will be deleted from child table. All existing data will be replaced with imported data.




2 comments:

darkterror said...

hi

is it neccessary to drop the user..while schema refreshing from one db to another database(db).
Please suggest if there is another way.

Oracle DBA said...

No its not compulsory to drop a schema u can also do without dropping by setting the above parameter according to ur need.

http://chandu208.blogspot.com/2012/01/data-pump-schema-refresh.html

Post a Comment

Auto Scroll Stop Scroll