AlderPump logo

Version 2.5

Full support for 32 and 64-bit clients.

Covers all features of Oracle 10.1 to 11.2

Usability improvements and bugfixes

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.

Prerequisites:
  1. SCOTT schema with tables EMP and DEPT
  2. scott_tables.dmp created on export step and located in DATA_PUMP_DIR
Demonstrated actions:
  1. Job Creation Wizard: Setting operation and mode, specifying dump file, specifying TABLE_EXISTS_ACTION, submitting job.
  2. Main window: Job creation, job launching, progress log, job completion.

Tips:

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

Selecting operation IMPORT and mode TABLES on Operations And Mode tab

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:

Specifying scott_tables.dmp as input file

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).

Setting TABLE_EXISTS_ACTION to REPLACE on Parameters tab

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.

Creating and launching job. The job successfully completes.