TABLE_EXISTS_ACTION parameter option of impdp

TABLE_EXISTS_ACTION parameter option of impdp

Impdp has a parameter option TABLE_EXISTS_ACTION. The help = y is interpreted:

Action to take if imported object already exists.
Valid keywords are: APPEND, REPLACE, [SKIP] and TRUNCATE.
 
There is a sentence in the official document:
"Only objects created by the Import will be remapped. In particle, the tablespaces for preexisting tables will not be remapped if TABLE_EXISTS_ACTION is set to SKIP, TRUNCATE, or APPEND .".
Only objects created by import will be remapped. If SKIP, TRUNCATE, or APPEND is used, the tablespace corresponding to the existing table will not change.
 
Official documentation (Oracle? The description of this parameter in Database Utilities11g Release 2 (11.2) is as follows:
The purpose of this parameter is to tell impdp whether the table to be created already exists in the database.
The default value is skip, but if CONTENT = DATA_ONLY is set, the default value is APPEND, not SKIP.
TABLE_EXISTS_ACTION = [SKIP | APPEND | TRUNCATE | REPLACE]
SKIP: SKIP this table and continue to the next object. If the DATA_ONLY parameter is set for CONTENT, SKIP cannot be used.
APPEND: loads data to the object, but does not affect existing rows.
TRUNCATE: Delete existing rows and load all data.
REPLACE: drop an existing table, and then create and load data. If DATA_ONLY is set for CONTENT, REPLACE cannot be used.
 
There are other considerations:
1. Use TRUNCATE or REPLACE to ensure that no other reference constraints exist for all involved table rows. It is easy to understand. For example, if the target table is associated with another table with a foreign key, but only this table is imported, this relationship may be broken.
2. Using SKIP, APPEND, and TRUNCATE, the dependent objects of existing tables, such as indexes, grants authorization, triggers, and constraints, will not be modified. For REPLACE, if the dependent object is not explicitly or implicitly excluded using EXCLUDE and EXCLUDE exists in the dump file, it will be drop and then create again.
3. When APPEND or TRUNCATE is used, some checks are performed before the operation is executed to ensure compatibility between the source dump and the existing table, including:
(1) If an existing table has active constraints and triggers, the data is loaded using the external table access method. If any row violates the constraints, the loading fails and no data is loaded. Of course, you can skip this step using DATA_OPTIONS = SKIP_CONSTRAINT_ERRORS.
(2) If you need to load data that may violate the constraints, consider disable these constraints first, load the data, delete these problematic records, and then enable the constraints.
(3) When APPEND is used, data is loaded to the new space, even if the existing space can be reused. After the data can be loaded, compress the data.
In addition, if the data pump finds that the original and target tables do not match (for example, the number of columns in the two tables is different, or one column in the target table is not in the original table ), it will compare the column names of the two tables. If at least one of the two tables has the same column, the data in this column will be imported (provided that the data type is compatible ). This approach also has some restrictions,
(A) if you use the network parameter to import data, you cannot use it.
(B) the types of the following columns cannot be deleted: column objects, column attributes, nested columns, and primary key-based reference columns.
Also, TRUNCATE cannot be used for clustering tables.
 
Experiment with the above four options for a table to see the difference.
 
Test Table:
Create table test (id number );
Insert into test values (1 );
Commit;
Expdp user_exp/user_exp directory = EXPDP_DIR dumpfile = user_exp.dmp
Insert into test values (2 );
Commit;
In this case, user_exp.dmp contains the test table with an id = 1 record. The table contains two records: id = 1 and id = 2.
 
REPLACE options:
Impdp user_exp/user_exp TABLE_EXISTS_ACTION = replace dumpfile = user_exp.dmp directory = expdp_dir
SQL> select * from test;
ID
----------
1
At this time, the table only has records with id = 1, indicating that the test table is overwritten with dmp.
SKIP options:
Impdp user_exp/user_exp TABLE_EXISTS_ACTION = skip dumpfile = user_exp.dmp directory = expdp_dir
ORA-39151: Table "USER_EXP". "TEST" exists. All dependent metadata and data will be skipped due to table_exists_action of skip
SQL> select * from test;
ID
----------
1
2
At this time, the import error indicates that the skip already exists and the test table remains unchanged.

APPEND options:
Impdp user_exp/user_exp TABLE_EXISTS_ACTION = append dumpfile = user_exp.dmp directory = expdp_dir
ORA-39152: Table "USER_EXP". "TEST" exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
SQL> select * from test;
ID
----------
1
2
1
Although an error is reported, the test record is still inserted, and the error message is that the data will be appended to an existing table. However, metadata with dependency will be ignored.
 
TRUNCATE options:
Impdp user_exp/user_exp TABLE_EXISTS_ACTION = truncate dumpfile = user_exp.dmp directory = expdp_dir
ORA-39153: Table "USER_EXP". "TEST" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate
SQL> select * from test;
ID
----------
1
The error message indicates that the object has been truncate, but metadata with dependency is ignored.

Simple Backup Using impdp and expdp provided by Oracle

The skip_constraint_errors option of Oracle impdp skips the unique constraint error.

Expdp/impdp use the version parameter for cross-version data migration