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.

5 thoughts on “Importing Into Existing Tables”

    1. Hi Peter,
      Thanks for the comment.
      You are right, it is important to mention. However, It shouldn’t help with the truncate part, as you cannot truncate a table if there is an active FK referring it. This may help when some of the rows are violating a unique constraint or foreign key, but we don’t care about that and want to rest of the rows to be imported anyway.
      Liron

      Like

  1. Hi Amitzil,
    Of course, all it depends on requirements, time and etc.
    Maybe you could create a first step to merge the parent tables (lmpdp to aux tables then merge them) then just append the child tables.
    I’ve never do that, just a thought.
    Best regards.

    Like

    1. Hi Fabio,
      Thanks for the comment. In this case, there are quite a few tables involved (around 50) and they have all kind of FKs between them and between them and other tables as well. I think your suggestion might be a bit complex. Also, if you append the data, you might get duplicate rows, with I really don’t want to have. I’d like to replace the existing data, but need to handle the FK issues.
      Liron

      Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s