Question: What is the purpose of
the table_exists_action parameter in the import of
data pump (impdp)? Please show an example of
table_exists_action.
Answer:
Using a data pump import (impdp), there
are four possible actions for table_exists_action:
TABLE_EXISTS_ACTION = {SKIP | APPEND | TRUNCATE |
REPLACE}
As the name implies, table_exists_action is used
in impdp when the table already exists in the
database where the import is being executed.
table_exists_action=skip:
This says to ignore the data in the import file and leave
the existing table untouched. This is the default and
it is not a valid argument if you set content=data_only.
table_exists_action=append:
This says to append the export data onto the
existing table, leaving the existing rows and adding the new
rows from the dmp file. Of course, the number and
types of the data columns must match to use the append
option. Just like the append hint,
Oracle will not re-user any space on the freelists and the
high-water mark for the table will be raised to accommodate
the incoming rows.
table_exists_action=truncate:
This says to truncate the existing table rows,
leaving the table definition and replacing the rows from the
expdp dmp file being imported. To use this option you
must not have any referential integrity (constraints) on the
target table. You use the
table_exists_action=truncate when the existing
table columns match the import table columns. The
truncate option cannot be used over a db link or with a
cluster table.
table_exists_action=replace:
This says to delete the whole table and
replace both the table definition and rows from the import
dmp file.
To use this option you must not have any referential
integrity (constraints) on the target table. You use
the table_exists_action=replace when the existing
table columns do not match the import table columns.
The default value is table_exists_action=skip,
meaning that any existing tables will be left alone upon
import. If you have parent-child constraints defined,
it is a best practice to disable or drop the constraints,
import the data, and then re-add (or re-enable) the table
constraints. This prevents transient referential
integrity errors.
Here is an example of using the impdp command-line
utility:
root> impdp hr/hr tables=employees
directory=mydir dumpfile=expschema.dmp
table_exists_action=replace
