TABLE_EXISTS_ACTION
Default: SKIP
(Note that if CONTENT=
DATA_ONLY
is specified, then the default is APPEND
, not SKIP
.)
Tells Import what to do if the table it is trying to create already exists.
TABLE_EXISTS_ACTION=[SKIP | APPEND | TRUNCATE | REPLACE]
The possible values have the following effects:
-
SKIP
leaves the table as is and moves on to the next object. This is not a valid option if theCONTENT
parameter is set toDATA_ONLY
. -
APPEND
loads rows from the source and leaves existing rows unchanged. -
TRUNCATE
deletes existing rows and then loads rows from the source. -
REPLACE
drops the existing table and then creates and loads it from the source. This is not a valid option if theCONTENT
parameter is set toDATA_ONLY
.
The following considerations apply when you are using these options:
-
When you use
TRUNCATE
orREPLACE
, ensure that rows in the affected tables are not targets of any referential constraints. -
When you use
SKIP
,APPEND
, orTRUNCATE
, existing table-dependent objects in the source, such as indexes, grants, triggers, and constraints, are not modified. ForREPLACE
, the dependent objects are dropped and re-created from the source, if they were not explicitly or implicitly excluded (usingEXCLUDE
) and they exist in the source dump file or system. -
When you use
APPEND
orTRUNCATE
, checks are made to ensure that rows from the source are compatible with the existing table before performing any action.If the existing table has active constraints and triggers, then it is loaded using the external tables access method. If any row violates an active constraint, then the load fails and no data is loaded. You can override this behavior by specifying
DATA_OPTIONS=SKIP_CONSTRAINT_ERRORS
on the Import command line.If you have data that must be loaded, but may cause constraint violations, then consider disabling the constraints, loading the data, and then deleting the problem rows before reenabling the constraints.
-
When you use
APPEND
, the data is always loaded into new space; existing space, even if available, is not reused. For this reason, you may want to compress your data after the load.
Note:
When Data Pump detects that the source table and target table do not match (the two tables do not have the same number of columns or the target table has a column name that is not present in the source table), it compares column names between the two tables. If the tables have at least one column in common, then the data for the common columns is imported into the table (assuming the datatypes are compatible). The following restrictions apply:-
This behavior is not supported for network imports.
-
The following types of columns cannot be dropped: column objects, object attributes, nested table columns, and ref columns based on a primary key.
-
TRUNCATE
cannot be used on clustered tables.
The following is an example of using the TABLE_EXISTS_ACTION
parameter. You can create the expfull.dmp
dump file used in this example by running the example provided for the Export FULL
parameter. See "FULL".
> impdp hr TABLES=employees DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp TABLE_EXISTS_ACTION=REPLACE