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:
We are importing tables, hence operation would be IMPORT and mode would be TABLE.
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:
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).
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.
© 2007-2012 AlderProgs Consulting Ltd.