Importing tables which do not yet exist in target schema

Download job definition

In the previous tutorial we've exported two tables EMP and DEPT from schema SCOTT. We could do that to copy them to another database, or for backup purposes. Now, let's see how these tables can be restored. In this scenario we assume tables do not exist; next tutorial shows how to deal with the case when they do.

So, go ahead and drop EMP and DEPT from schema SCOTT:

  DROP TABLE scott.emp;
  DROP TABLE scott.dept;

Prerequisites:

SCOTT schema without tables EMP and DEPT
scott_tables.dmp created on export step and located in DATA_PUMP_DIR

Demonstrated actions:

Job Creation Wizard: Setting operation and mode, specifying dump file, submitting job.
Main window: Job creation, job launching, progress log, job completion.

Tips:

It doesn't matter which user runs import: by default tables placed into schema they were exported from.
But to import objects into another schema, user needs DATAPUMP_IMP_FULL_DATABASE role.

Step 1/3: Selecting Operation and Mode

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/3: Specifying input file

Import needs dump file containing the data, we type name of the file created in previous chapter. The file resides in directory to which it has been exported: DATA_PUMP_DIR. Since object type was already set to "Dump file", we don't need to change it. We could specify log file, but since our operation is ad-hoc and AlderPump displays same messages as log file would contain, we don't. All parameter were set, and we proceed straight to job creation by clicking "Create".

Specifying scott_tables.dmp as intput file

Step 3/3: Running import

As usual, newly created job resides 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 are imported into their schema of origin: SCOTT. Import also creates constraints, triggers, and indexes if they are present in the export file.

Creating and launching job. The job successfully completes.

If file name was misspelled, we'd get get error message below while trying to define job:

add_file 'DATA_PUMP_DIR':'scoot_tables.dmp', type 1, size ''
ORA-39001: invalid argument value
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3444
ORA-06512: at "SYS.DBMS_DATAPUMP", line 3693
ORA-06512: at line 2

To correct spelling errors, return to Create Job dialog. On "File" panel select the dump file, correct the typo, and click "Add dump". Then highlight line with misspelled name and hit "DEL" button on your keyboard to remove it.