EZ Guide to Oracle Data Pump

Oracle Data Pump (DP) was introduced in 10g to replace the legendary export / import utility (Still available). The Data Pump although takes more time to start is more flexible and manageable and provides greater performance.

Data Pump consist of the following components:

Data Pump API: DBMS_DATAPUMP (creates and monitors DP Jobs).
Metadata API : DBMS_METADATA (Provides objects definitions to DP).
Client Tool: expdp and impdp (Makes calls to DBMS_DATAPUMP).
Data movement API: Uses direct path API (DPAPI) to move data when possible.

The first you need to do is to setup a location (Oracle Directory) where the DP files will be stored.

SQL> create directory dumplocation as '/oradata/dumpfiles';
SQL> grant read, write on directory dumplocation to scott;

In addition a default directory can be created for data pump operations in the database as follows (once created privileged users with EXP_FULL_DATABASE or IMP_FULL_DATABASE privilege do not need to specify a directory):

SQL> create directory DATA_PUMP_DIR as '/oradata/dumpfiles';

Note: The name of the directory must be DATA_PUMP_DIR.

There are 5 different types of DP import and exports:

1. Database (The default performed by specifying the FULL=Y parameter)
2. Tablespaces (Performed by specifying the TABLESPACES parameter)
3. Schema (Performed by specifying the SCHEMAS parameter)
4. Table (Performed by specifying the TABLES parameter)
5. Transportable Tablespace (Performed by specifying the TRANSPORT_TABLESPACES parameter)

EXPDP:

$ expdp directory=dumplocation
..
..
Username: scott/tiger
..
..
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 14:49
$

Since we did not specify any other parameters, the expdp used default values for the file names (expdat.dmp, export.log), did schema-level export (login schema), calculated job estimation using blocks method, used default job name (SYS_EXPORT_SCHEMA_01), and exported both data and metadata.

List of the expdp parameters


Keyword Description (Default)
------------------------------------------------------------------------------
ATTACH Attach to existing job, e.g. ATTACH [=job name].
COMPRESSION Reduce size of dumpfile contents where valid
keyword values are: (METADATA_ONLY) and NONE.
CONTENT Specifies data to unload where the valid keywords are:
(ALL), DATA_ONLY, and METADATA_ONLY.
DIRECTORY Directory object to be used for dumpfiles and logfiles.
DUMPFILE List of destination dump files (expdat.dmp),
e.g. DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp.
ENCRYPTION_PASSWORD Password key for creating encrypted column data.
ESTIMATE Calculate job estimates where the valid keywords are:
(BLOCKS) and STATISTICS.
ESTIMATE_ONLY Calculate job estimates without performing the export.
EXCLUDE Exclude specific object types, e.g. EXCLUDE=TABLE:EMP.
FILESIZE Specify the size of each dumpfile in units of bytes.
FLASHBACK_SCN SCN used to set session snapshot back to.
FLASHBACK_TIME Time used to get the SCN closest to the specified time.
FULL Export entire database (N).
HELP Display Help messages (N).
INCLUDE Include specific object types, e.g. INCLUDE=TABLE_DATA.
JOB_NAME Name of export job to create.
LOGFILE Log file name (export.log).
NETWORK_LINK Name of remote database link to the source system.
NOLOGFILE Do not write logfile (N).
PARALLEL Change the number of active workers for current job.
PARFILE Specify parameter file.
QUERY Predicate clause used to export a subset of a table.
SAMPLE Percentage of data to be exported;
SCHEMAS List of schemas to export (login schema).
STATUS Frequency (secs) job status is to be monitored where
the default (0) will show new status when available.
TABLES Identifies a list of tables to export - one schema only.
TABLESPACES Identifies a list of tablespaces to export.
TRANSPORT_FULL_CHECK Verify storage segments of all tables (N).
TRANSPORT_TABLESPACES List of tablespaces from which metadata will be unloaded.
VERSION Version of objects to export where valid keywords are:
(COMPATIBLE), LATEST, or any valid database version.

The following commands are valid while in interactive mode.
Note: abbreviations are allowed

Command Description
------------------------------------------------------------------------------
ADD_FILE Add dumpfile to dumpfile set.
CONTINUE_CLIENT Return to logging mode. Job will be re-started if idle.
EXIT_CLIENT Quit client session and leave job running.
FILESIZE Default filesize (bytes) for subsequent ADD_FILE commands.
HELP Summarize interactive commands.
KILL_JOB Detach and delete job.
PARALLEL Change the number of active workers for current job.
PARALLEL=.
START_JOB Start/resume current job.
STATUS Frequency (secs) job status is to be monitored where
the default (0) will show new status when available.
STATUS[=interval]
STOP_JOB Orderly shutdown of job execution and exits the client.
STOP_JOB=IMMEDIATE performs an immediate shutdown of the
Data Pump job.

IMPDP

$ impdp dumpfile=expdat.dmp
..
username: scott/tiger
..
Job "SCOTT"."SYS_IMPORT_FULL_01" successfully completed at 07:09

List of impdp Parameters

Keyword Description (Default)
------------------------------------------------------------------------------
ATTACH Attach to existing job, e.g. ATTACH [=job name].
CONTENT Specifies data to load where the valid keywords are:
(ALL), DATA_ONLY, and METADATA_ONLY.
DIRECTORY Directory object to be used for dump, log, and sql files.
DUMPFILE List of dumpfiles to import from (expdat.dmp),
e.g. DUMPFILE=scott1.dmp, scott2.dmp, dmpdir:scott3.dmp.
ENCRYPTION_PASSWORD Password key for accessing encrypted column data.
This parameter is not valid for network import jobs.
ESTIMATE Calculate job estimates where the valid keywords are:
(BLOCKS) and STATISTICS.
EXCLUDE Exclude specific object types, e.g. EXCLUDE=TABLE:EMP.
FLASHBACK_SCN SCN used to set session snapshot back to.
FLASHBACK_TIME Time used to get the SCN closest to the specified time.
FULL Import everything from source (Y).
HELP Display help messages (N).
INCLUDE Include specific object types, e.g. INCLUDE=TABLE_DATA.
JOB_NAME Name of import job to create.
LOGFILE Log file name (import.log).
NETWORK_LINK Name of remote database link to the source system.
NOLOGFILE Do not write logfile.
PARALLEL Change the number of active workers for current job.
PARFILE Specify parameter file.
QUERY Predicate clause used to import a subset of a table.
REMAP_DATAFILE Redefine datafile references in all DDL statements.
REMAP_SCHEMA Objects from one schema are loaded into another schema.
REMAP_TABLESPACE Tablespace object are remapped to another tablespace.
REUSE_DATAFILES Tablespace will be initialized if it already exists (N).
SCHEMAS List of schemas to import.
SKIP_UNUSABLE_INDEXES Skip indexes that were set to the Index Unusable state.
SQLFILE Write all the SQL DDL to a specified file.
STATUS Frequency (secs) job status is to be monitored where
the default (0) will show new status when available.
STREAMS_CONFIGURATION Enable the loading of Streams metadata
TABLE_EXISTS_ACTION Action to take if imported object already exists.
Valid keywords: (SKIP), APPEND, REPLACE and TRUNCATE.
TABLES Identifies a list of tables to import.
TABLESPACES Identifies a list of tablespaces to import.
TRANSFORM Metadata transform to apply to applicable objects.
Valid transform keywords: SEGMENT_ATTRIBUTES, STORAGE
OID, and PCTSPACE.
TRANSPORT_DATAFILES List of datafiles to be imported by transportable mode.
TRANSPORT_FULL_CHECK Verify storage segments of all tables (N).
TRANSPORT_TABLESPACES List of tablespaces from which metadata will be loaded.
Only valid in NETWORK_LINK mode import operations.
VERSION Version of objects to export where valid keywords are:
(COMPATIBLE), LATEST, or any valid database version.
Only valid for NETWORK_LINK and SQLFILE.

Table Exports/Imports

The TABLES parameter is used to specify the tables that are to be exported. The following is an example of the table export and import syntax:

expdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=expdpEMP_DEPT.log

impdp scott/tiger@db10g tables=EMP,DEPT directory=TEST_DIR dumpfile=EMP_DEPT.dmp logfile=impdpEMP_DEPT.log

The TABLE_EXISTS_ACTION=APPEND parameter allows data to be imported into existing tables.

Schema Exports/Imports

The OWNER parameter of exp has been replaced by the SCHEMAS parameter which is used to specify the schemas to be exported. The following is an example of the schema export and import syntax:

expdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log

impdp scott/tiger@db10g schemas=SCOTT directory=TEST_DIR dumpfile=SCOTT.dmp logfile=impdpSCOTT.log

Database Exports/Imports

The FULL parameter indicates that a complete database export is required. The following is an example of the full database export and import syntax:

expdp system/password@db10g full=Y directory=TEST_DIR dumpfile=DB10G.dmp logfile=expdpDB10G.log

impdp system/password@db10g full=Y directory=TEST_DIR dumpfile=DB10G.dmp logfile=impdpDB10G.log

I was debating if adding this or not to this document because this suppose to be an EZ guide, but since these are my 2 favorite Data Pump features I decided to add it.

Few years ago you needed to wait for a long import / export to finish before going home and logout of you account or turn off your desktop. Or simple you got an alectric failure while the import was taking place and now you have to clean up and start all over (I have been there and done that, it is very frustrating).

Attach and Detach from a session

The first you need to know is that the core of the DP import or export operation is taking place using a database job, so it does not really depend of your client connection, cool isn't it?. In
other words your job will continue to run even if the client disconnects.

DP works in 2 modes, logging similar to the old import / export and interactive mode. To enter into the interactive mode just control-C while the import or export is taking place.

In the example below I pressed Control-C as soon as the export started.

oracle@mtl01dbatlora03:/opt/oracle/product/10.2.0.1/rdbms/log> expdp system/password

Export: Release 10.2.0.3.0 - Production on Friday, 07 March, 2008 11:31:24

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Release 10.2.0.3.0 - Production
Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/********
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Export> status

Job: SYS_EXPORT_SCHEMA_01
Operation: EXPORT
Mode: SCHEMA
State: EXECUTING
Bytes Processed: 0
Current Parallelism: 1
Job Error Count: 0
Dump File: /opt/oracle/product/10.2.0.1/rdbms/log/expdat.dmp
bytes written: 4,096

Worker 1 Status:
State: EXECUTING
Object Schema: SYSTEM
Object Name: TEMPLATE$_TARGETS_S
Object Type: SCHEMA_EXPORT/SEQUENCE/SEQUENCE
Completed Objects: 18
Total Objects: 18
Worker Parallelism: 1

Export> exit_client

Then to re-attach all I have to do is

expdp system/password attach=SYS_EXPORT_SCHEMA_01

export>

and I will be on my way, if I decide to continue in logging mode all I next to do is

export> continue_client

The other cool feature of Data Pump is that in most of the cases you can stop the job do anything you forgot in the database (like creating a specific tablespace) and then re-start it. ``

Below is a table of some of the commands you can execute while in interactive mode.