Importing Into Existing Tables

I did some testing with impdp for a client. They asked me to write a procedure to import a set of tables from a production environment to a testing database. The import will include only a few tables, but not all, and will be performed to an already existing test environment that contains a full schema. And this process should be performed on a regular basis, so I looked for the best and easiest solution.

Because of the requirements I mentioned above, I looked at different impdp options. I knew that impdp has the option to overwrite existing tables, but wanted to know how it works with constraints as well, so I did some testings and this is what I found.
Impdp has a parameter called TABLE_EXISTS_ACTION. It accepts 4 values:

• SKIP – the default. If a table exists, the import will simply skip this table, leaving it untouched, and will print a message: “Table <TAB> exists. All dependent metadata and data will be skipped due to table_exists_action of skip”
• APPEND – will add the data from the dump file to the existing table without touching the existing data. This might lead to unique constraint violations
• TRUNCATE – will truncate the table and then insert the data from the dump file
• REPLACE – will drop the existing table, recreate it from the dump file and insert the data from the dump file

Obviously I should use either TRUNCATE or REPLACE, but which one?

• If the table structure in the prod might be different and I’d like to have the new structure, it’s quite obvious that I need to use REPLACE
• If the table structure in the prod might be different and I’d like to keep the existing structure, it’s quite obvious that I need to use TRUNCATE

And what about foreign keys?
When using TRUNCATE, the import will not handle constraints. This means that if we have FK referencing the table we are importing, the import will get “ORA-39120: Table <TAB> can’t be truncated, data will be skipped” and “ORA-02266: unique/primary keys in table referenced by enabled foreign keys”. In this case the table will stay untouched. This happens even if there are 2 tables (parent and child) with a foreign key between them and we import both of them in the same impdp command.
If we want to use TRUNCATE in this case, we’ll have to disable all FK constraints from any table to the tables we’re importing before the import, and enable them after the import. Note that if the data is not consistent between the two environments, we might not be able to enable the FK as we might have missing parent keys.
When using REPLACE, the import will drop the target tables regardless of any foreign keys. If there are FK constraints referencing the table we’re importing, they will be deleted. Note that FK constraints will be created on the tables we’re importing as part of the import.
For example:
We have 3 tables: P, C1 and C2. Both C1 and C2 have FK to P. Assume that we’re importing P and C1, but not C2, and we’re using REPLACE. In this case, the import will drop P and C1 with all the FKs referencing them. Then the import will create P and C1 with all of their constraints. At the end, we will have P and C1 with the data from the dump file and C2 with the data it had before. The import will also create P’s primary key and C1’s foreign key, but C2’s foreign key will be lost as it is deleted as part of the process and won’t be recreated.