Export / Import

There are two sets of Oracle Export/Import utilities:

• [Old] Original Export/Import (exp/imp);
• [New] Oracle Data Pump Export/Import (expdp/impdp);

In general, you should use new Oracle Data Pump utilities (available since Oracle Database 10g) because they are superior. But sometimes you have to use old utilities. For example:

• You import files that were created by the original export utility;
• You export files that will be imported by the original import utility;

The table below shows which exp/imp releases to use when moving data between different releases of the Oracle database.

Note that files generated by the original exp utility cannot be imported with the Data Pump Import utility, and vice versa.

Data Pump Import cannot read a dump file created by a database version newer than the current version, unless that dump file was created with the VERSION param set to the version of the target database.

Export / Import (original, old)

Full database export/import requires powerful privs (see EXP_FULL_DATABASE role, IMP_FULL_DATABASE role) that regular users should never have. Regular users nowdays usually have only CREATE SESSION priv and can export their own objects, but the import of anything requires the whole bunch of system privs like CREATE TABLE, CREATE INDEX, etc; the old (outdated) RESOURCE role had them all.

Full database export:

exp PARFILE=param.dat

where parameter file (param.dat):

USERID=SYSTEM/pass@s3
FULL=Y
COMPRESS=Y
CONSISTENT=Y
LOG=export.log

If this is not a scheduled / automated procedure, remove password (to be prompted for it at the right moment); otherwise set restrictive permissions on param.dat (anyway this is not safe).

Export all objects owned by user pro17 (the whole user’s schema):

exp system owner=pro17

Export table usrlist owned by appadm:

exp SYSTEM TABLES=APPADM.USRLIST
GRANTS=Y INDEXES=N LOG=EXPORT.LOG

Being Oracle user dbsman export (over a network) some tables:

exp dbsman@s4 tables=(usrlist,jobs,depts)

Full database export, old style, Windows / Oracle 7.3:

exp73 SYSTEM@s2 FULL=Y LOG=EXPORT.LOG

Full database import (requires some preliminary work); full db export file (expdat.dmp) must be present in the current working directory:

imp SYSTEM FULL=Y LOG=IMPORT.LOG

Being Oracle user dbsman import some of your tables from the default export file (expdat.dmp, must be in the current working dir):

imp dbsman tables=(usrlist,jobs,depts)

Import using parameter file:

imp PARFILE=param.dat

Parameter file to be used to import a table to another schema assuming you have a full database export file expdat.dmp (you'll be prompted for SYSTEM's password):

USERID=system
TABLES=(hw_computers)
FROMUSER=pro08
TOUSER=dbsman
LOG=imp002.log

Parameter file to be used to import some user tables from a full database export file (database is available through srv2 alias; user must exist; you'll be prompted for SYSTEM's password):

USERID=system@srv2
TABLES=(usrlist,usrlist2,hw_ip_addr)
FROMUSER=eco30
TOUSER=eco30
LOG=imp005.log

Param file to be used to import the whole user's schema from a full db exp file (user must exist; you'll be prompted for SYSTEM's password):

USERID=system
FROMUSER=a301
TOUSER=a301
LOG=imp007.log

If tables being imported already exist in the database and there are referential constraints between them, some/all rows may be rejected due to constraint violation. Tables are imported in the order of their location in exp dump file, which can be wrong for constraints. In this case you should import related tables separately, in the right order. Or, you can disable constraints and re-enable after import. Also, if tables exist, use IGNORE=y, otherwise when imp fails to create table, it does not attempt to insert rows - just skips to the next object.

USERID=SYSTEM
FILE=expdat.dmp
GRANTS=n
IGNORE=y
BUFFER=262144
FROMUSER=dbsman
TOUSER=dbsman
TABLES=(tab0202,tab0220)
LOG=imp0029.log

Export / Import (Data Pump)

Oracle Data Pump is available only on Oracle Database 10g release 1 (10.1) and later. Oracle Data Pump is made up of three distinct parts:

1. The command line clients, expdp and impdp.
2. The DBMS_DATAPUMP PL/SQL pkg (Data Pump API).
3. The DBMS_METADATA PL/SQL pkg (Metadata API).

The Data Pump clients, expdp and impdp, invoke the Data Pump Export utility and Data Pump Import utility, respectively. Dump files generated by the Data Pump Export utility are not compatible with dump files generated by the original Export utility. Therefore, old-style exports cannot be imported with the Data Pump Import utility.

The Data Pump is more effective than original exp / imp (due to parallel execition, etc), can unload into a group of files [of the specified length], however, it requires more undo space.

To make full use of Data Pump technology, you must have EXP_FULL_DATABASE and IMP_FULL_DATABASE roles.

Data Pump supports 2 access methods: direct path and external tables. Both methods support the same external data representation, so data that is unloaded with one method can be loaded using the other method. Data Pump automatically chooses the best. To handle external tables Data Pump uses ORACLE_DATAPUMP access driver.

Warning!

The files that Data Pump creates when it uses external tables are not compatible with files created when you manually create an external table!

Because Data Pump is server-based, dump files, log files, and SQL files are accessed relative to server-based directory paths. It means you may need to create OS dirs and Oracle directory objects. A dir object maps a name to a dir path on the filesystem:

CREATE DIRECTORY dpump_dir1 AS
'/u06/exp/datafiles';

The creator (usually DBA) can give permissions to other users:

GRANT READ, WRITE ON DIRECTORY dpump_dir1 TO pro17;

These permissions allow Oracle to read / write files on your behalf. To access these files outside of the Oracle you need OS permissions.

The default dir object, DATA_PUMP_DIR, is created at database creation and is available only to privileged users. To see the path:

SELECT directory_name, directory_path

FROM dba_directories

WHERE directory_name = 'DATA_PUMP_DIR';

The client can also set the env variable DATA_PUMP_DIR, provided it maps to an existing OS dir and is accessible.

The Data Pump Export / Import modes:

1. Full (param name is FULL).
2. Schema (param name is SCHEMAS).
3. Table (param name is TABLES).
4. Tablepsace (param name is TABLESPACES).
5. Transportable Tablespace (TRANSPORT_TABLESPACES).

You can interact with Data Pump using following modes:

1. Logging mode / command line interface.
2. Logging mode / parameter file interface.
3. Interactive command mode.

Warning!

Never invoke Import as SYSDBA, except at the request of Oracle tech support!

There are 2 sources of import: dump file set and another database (network import). If import source is dump and mode is not specified, then import attempts to load the entire dump file set in the mode in which the export operation was run.

Logging mode parameters (Export and Import)

ATTACH   attaches client session to an existing exp/imp job and automatically places you in the interactive cmd interface;

CONTENT={ DATA_ONLY | METADATA_ONLY | ALL }   specifies what to unload (load) during export (import); default is ALL;

DIRECTORY   specifies the default location (dir object) for dump file sets and log files (default is DATA_PUMP_DIR);

DUMPFILE   specifies the names, and optionally, the dir objects of dump files (default is expdat.dmp);

ENCRYPTION_PASSWORD   specifies a key for encrypting encrypted column data during export or accessing encrypted column data during import;

ESTIMATE={ BLOCKS | STATISTICS }   specifies the method that will be used to estimate how much disk space each table in the export job will consume, or how much data will be generated in a network import operation; for import it can be used only when NETWORK_LINK is specified; the default is BLOCKS;

EXCLUDE   enables you to filter the metadata that is exported (imported) by specifying objects and object types that you want to exclude (see examples);

FLASHBACK_SCN   specifies the system change number that export (import) will use to enable the Flashback Query utility;

FLASHBACK_TIME   the SCN that most closely matches the specified time is found, and this SCN is used to enable the Flashback Query utility;

FULL={ y | n }   specifies a full database export (import); the following system schemas are not exported because the metadata they contain is exported as part of other objects in the dump file set: SYS, ORDSYS, EXFSYS, MDSYS, DMSYS, CTXSYS, ORDPLUGINS, LBACSYS, XDB, SI_INFORMTN_SCHEMA, DIP, DBSNMP, WMSYS; grants on objects owned by the SYS schema are never exported; default is n;

HELP   displays online help;

INCLUDE   enables you to filter the metadata that is exported (imported) by specifying objects and object types; during export dependent objects and grants are also exported;

JOB_NAME   identifies the export (import) job in subsequent actions (ATTACH), and becomes the name of the master table in the current user's schema (the master table is used to control the job);

LOGFILE   specifies the name for the log file of exp (imp) job; default is export.log (import.log);

NOLOGFILE={ y | n }   specifies whether to suppress creation of a log file (default is n);

PARALLEL   (Enterprise Edition only) specifies the max num of threads of active execution operating on behalf of the exp / imp job (default is 1);

PARFILE   the name of an export (import) param file;

QUERY   allows you to filter data that is exported (imported) by specifying a clause for a SQL SELECT stmt, which is applied to all tables in the exp (imp) job or to a specific table;

SCHEMAS   enables a schema-mode export (import) and specifies a comma-separated list of schemas (by default, current user's schema); only priv users can exp (imp) other user's schemas; this is the default mode for export and network-based import;

STATUS   specifies how frequently (in seconds) the job status info is written to stdout; default is 0 (never);

TABLES   invokes a table-mode export (import) and specifies a comma-separated list of tables (partitions, subpartitions);

TABLESPACES   enables a tablespace-mode exp (imp) and specifies a list of TS names to be exported (imported);

TRANSPORT_FULL_CHECK={ y | n }   specifies whether or not to verify that the specified transportable tablespace set has no dependencies; default is n;

TRANSPORT_TABLESPACES   specifies that you want to perform a transportable tablespace mode export (import);

VERSION   specifies the version of database objects to be exported (imported); can be used to create a dump file set compatible with a prev release of Oracle (does not mean that dump set can be used with versions prior to 10.1); default is COMPATIBLE;

Export only parameters

COMPRESSION={ METADATA_ONLY | NONE }   specifies whether to compress metadata before writing to the dump file set; NONE disables compression for the entire unload (default is METADATA_ONLY);

ESTIMATE_ONLY={ y | n }   instructs Export to estimate the space that a job would consume, without actually performing export (default is n);

FILESIZE   the max size of each dump file; if the size is reached for any member of the dump file set, that file is closed and an attempt is made to create a new one (file specification must contain a substitution variable); default is 0 (unlimited);

SAMPLE   specifies the percentage of data to be sampled and unloaded from the source database;

Import only parameters

NETWORK_LINK   enables a network import when you specify the name of a valid database link to a source system;

REMAP_DATAFILE   changes the name of the source datafile to the target in all SQL stmts where source datafile is referenced: CREATE TABLESPACE | LIBRARY | DIRECTORY;

REMAP_SCHEMA   loads all objects from the source schema into a target schema;

REMAP_TABLESPACE   remaps all objects selected for import with persistent data in the source TS to be created in the target TS;

REUSE_DATAFILES={ y | n }   specifies whether or not the imp job should reuse existing datafiles for TS creation (default is n);

SKIP_UNUSABLE_INDEXES={ y | n }   specifies whether or not Import skips loading tables that have indexes that were set to the Index Unusable state (by either the system or the user); default is set by db config param of the same name;

SQLFILE   a file into which all SQL DDL that Import would have executed, based on other params, is written;

STREAMS_CONFIGURATION={ y | n }   specifies whether or not to import any general Streams metadata that may be present in the export dump file; default is y;

TABLE_EXISTS_ACTION={ SKIP | APPEND | TRUNCATE | REPLACE }   tells Import what to do if the table it is trying to create already exists; default is SKIP (or APPEND, if CONTENT=DATA_ONLY);

TRANSFORM   enables you to alter object creation DDL for specific objects, as well as for all applicable objects being loaded;

TRANSPORT_DATAFILES   a list of datafiles to be imported into the target database by a transportable-mode import (files must already have been copied from src db);

Interactive command mode

You can start the interactive cmd mode while export / import job is running (use another terminal or stop the current expdp / impdp session - this does not affect the job). To start an interactive session, run expdp / impdp with ATTACH param. In general, you must specify the job name (query DBA_DATAPUMP_JOBS, USER_DATAPUMP_JOBS), but if there is currently only one job in the user's schema, name is not required. For example:

expdp sysadm/psmtks72 ATTACH=sysadm.export_job

or

expdp sysadm/psmtks72 ATTACH

The same syntax is valid for impdb.

You will see the interactive cmd mode prompt Export> (Import>). There are following cmds available in interactive cmd mode:

ADD_FILE : add additional dump files (export only);

CONTINUE_CLIENT : exit interactive and enter logging mode;

EXIT_CLIENT : stop client session, but leave the job running;

FILESIZE : redefine the default size to be used for any subsequent dump files (export only);

HELP : display a summary of available cmds;

KILL_JOB : detach all currently attached client sessions and kill the current job;

PARALLEL : increase / decrease the number of active worker processes for the current job (Enterprise Edition only);

START_JOB : restart a stopped job to which you are attached;

STATUS : display detailed status for the current job and/or set status interval;

STOP_JOB : stop the current job for later restart;

Examples

To export 2 tables (items, equip) owned by alex:

expdp alex/dkmtv45s
TABLES=items,equip LOGFILE=x_items.log

To export the whole user's schema:

expdp alex/dkmtv45s
SCHEMAS=alex DUMPFILE=user_dumps:exp%U.dmp
FILESIZE=10M LOGFILE=exp_alex.log

Parameter DUMPFILE specifies both non-default dir and the name of the export file, %U is used to generate sequential numbers in filenames in case the export file exceeds 10M.

The schema-mode export done by DBA:

expdp dbsman SCHEMAS=alex,mistique

To export a user's schema excluding functions and procedures:

expdp alex/dkmtv45s
SCHEMAS=alex EXCLUDE=FUNCTION,PROCEDURE

To perform a full database export you must be a DBA or, at least, a privileged user (the one with EXP_FULL_DATABASE role):

expdp sysadm FULL=y

You can put all params in a parameter file and run export like this:

expdp sysadm/aspw6xhe PARFILE=param.dat

Parameter file (param.dat) for the full database export:

FULL=y
DIRECTORY=dpump_dir1
FILESIZE=650M
DUMPFILE=expful%U.dmp
LOGFILE=expfull.log

Another param.dat for the full database export:

FULL=y
EXCLUDE=TABLE:"LIKE 'APP_LOG%'"
EXCLUDE=SCHEMA:"='MISTIQUE'"
FILESIZE=1G
DUMPFILE=expful%U.dmp
PARALLEL=2
JOB_NAME=expfull

To estimate the space that would be consumed in a table-mode export, without actually performing the export operation:

expdp alex/dkmtv45s ESTIMATE_ONLY=y
TABLES=emp,dept LOGFILE=estimate.log

To import a user's schema:

impdp sysadm SCHEMAS=zorg TABLE_EXISTS_ACTION=REPLACE

To import tables:

impdp alex/dkmtv45s TABLES=items,equip
EXCLUDE=CONSTRAINT,GRANT LOGFILE=items.log

To perform a full database import excluding some schemas and objects:

impdp sysadm PARFILE=imp_param.dat

where imp_param.dat is like this:

FULL=y
DIRECTORY=dpump_dir1
EXCLUDE=SCHEMA:"='MANAGER4'"
EXCLUDE=TABLE:"LIKE 'RPT_AG%'"

To move an imported table to another schema:

impdp alex/dkmtv45s
TABLES=members REMAP_SCHEMA=zorg:alex

Unprivileged users can perform remap only if their schema is the target.