Importing tables which do not yet exist in target schema
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;
- SCOTT schema without tables EMP and DEPT
- scott_tables.dmp created on export step and located in DATA_PUMP_DIR
- 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.
We are importing tables, hence operation would be IMPORT and mode would be TABLE.
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".
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.
