Importing tables which already exist

Download job definition

In the table export tutorial we've created dump file containing tables EMP and DEPT from schema SCOTT. Then we've learned how to import these tables when they do not exist in target schema. Now we concentrate on scenario when tables are already there.

For imports of tables which already exist, impdp provides parameter TABLE_EXISTS_ACTION. The value can be SKIP, APPEND, TRUNCATE, or REPLACE, and its default depends on other settings. In short, SKIP ignores existing tables, APPEND adds data at the end of the table, TRUNCATE removes old data prior to import, and REPLACE drops and re-creates the entire table. Further details can be found in Oracle Utilities Reference.

In AlderPump, TABLE_EXISTS_ACTION parameter is configured on Parameters page; it is enabled or disabled depending on operation and mode, and AlderPump keeps track of its default value.

Tips:

• There is no equivalent to parameter TABLE_EXISTS_ACTION for non-table objects; import fails if it encounters, say, existing procedure.
• When appending or truncating data and tables structures do not match, Data Pump tries to be clever and only touches columns with same names and compatible types. This is usually a good thing because data can still be loaded into slightly modified tables.

We are importing tables, hence operation would be IMPORT and mode would be TABLE.

Step 2/4: Specifying input file

Like before, we are using the file created by table mode export. We type its name "scott_tables.dmp" and select default directory DATA_PUMP_DIR. We then add the dump file to the set:

Step 3/4: Setting TABLE_EXISTS_ACTION

So far things were the same as in previous tutorial. But now before creating the job, we need to tell Data Pump what to do when table being imported is already there. To recap, options are SKIP, APPEND, REPLACE or TRUNCATE, but not all of them are always applicable. The default is usually SKIP unless metadata is excluded by specifying DATA_ONLY - in which case the default is APPEND.

Here we've picked option "REPLACE"; this drops tables and re-creates them, then loads data. If imported table is referenced by another table via foreign key constraint, the constraint is dropped (i.e. Data Pump performs DROP TABLE CASCADE CONSTRAINTS when replacing).

Step 4/4: Running import

As usual, newly created job stays in DEFINING state until launched. We start the job and monitor messages displayed in log window. Again, they are the same messages impdp would print. Even though we launched the job as SYSTEM, tables get imported into their schema of origin: SCOTT. Import also creates constraints, triggers, and indexes if they were defined on the source table, but there is no indication that tables were dropped and created; we must figure that from job parameters.