Data Pump Export (expdp) and Data Pump Import(impdp) in Oracle

Data Pump Export (expdp) and Data Pump Import (impdp) are server-based rather than client-based as is the case for the original export (exp) and import (imp). Because of this, dump files, log files, and sql files are accessed relative to the server-based directory paths. Data Pump requires that directory objects mapped to a file system directory be specified in the invocation of the data pump import or export.

SQL> create directory expdp_dir as '/u01/backup/exports';

then grant read and write permissions to the users who will be performing the data pump export and import.

SQL> grant read,write on directory expdp_dir to system, user1, user2, user3;

Invoking Data Pump Export

You can invoke the data pump export using a command line. Export parameters can be specified directly in the command line.

Full Export Mode

A full export is specified using the FULL parameter. In a full database export, the entire database is unloaded. This mode requires that you have the EXP_FULL_DATABASE role. Example :

$ expdp system/ DIRECTORY=expdp_dir DUMPFILE=expfull.dmp FULL=y LOGFILE=expfull.og

Schema Export Mode

The schema export mode is invoked using the SCHEMAS parameter. If you have no EXP_FULL_DATABASE role, you can only export your own schema. If you have EXP_FULL_DATABASE role, you can export several schemas in one go.Optionally, you can include the system privilege grants as well.

$ expdp hr/hr DIRECTORY=expdp_dir DUMPFILE=schema_exp.dmp SCHEMAS=hr,sh,oe

Table Export Mode

This export mode is specified using the TABLES parameter. In this mode, only the specified tables, partitions and their dependents are exported. If you do not have the EXP_FULL_DATABASE role, you can export only tables in your own

schema. You can only specify tables in the same schema.

$ expdp hr/hr DIRECTORY=expdp_dir DUMPFILE=tables_exp.dmp TABLES=employees,jobs,departments

Invoking Data Pump Import

The data pump import can be invoked in the command line. The export parameters can be specified directly in the command line.

Full Import Mode The full import mode loads the entire contents of the source (export) dump file to the target database.However, you must have been granted the IMP_FULL_DATABASE role on the target database. The data pump import is invoked using the impdp command in the command line with the FULL parameter specified in the same command line.

$ impdp system/ DIRECTORY=expdp_dir DUMPFILE=expfull.dmp FULL=y LOGFILE=impfull.og Schema Import Mode

The schema import mode is invoked using the SCHEMAS parameter. Only the contents of the specified schemas are load into the target database. The source dump file can be a full, schema-mode, table, or tablespace mode export files. If you have a IMP_FULL_DATABASE role, you can specify a list of schemas to load into the target database.

$ impdp hr/hr DIRECTORY=expdp_dir DUMPFILE=expfull.dmp SCHEMAS=hr,sh,oe

Table Import Mode

This export mode is specified using the TABLES parameter. In this mode, only the specified tables, partitions and their dependents are exported. If you do not have the EXP_FULL_DATABASE role, you can import only tables in your own schema.

$ impdp hr/hr DIRECTORY=expdp_dir DUMPFILE=expfull.dmp TABLES=employees,jobs,departments

Oracle export and import

Their prime purpose is to move logical objects out of and into the database respectively - for example dumping all of the tables owned by a user to a single file is achieved using the exp utility.

Type 'imp help=y' or 'exp help=y' for a detailed explanation of the available options for these utilities.

Using exp:

To export the entire database to a single file dba.dmp in the current directory.

- Login to server

exp SYSTEM/password FULL=y FILE=dba.dmp LOG=dba.log CONSISTENT=y

or

exp SYSTEM/password PARFILE=params.dat

where params.dat contains the following information

FILE=dba.dmp

GRANTS=y

FULL=y

ROWS=y

LOG=dba.log

To dump a single schema to disk (we use the scott example schema here)

- Login to server which has an Oracle client

exp / FIlE=scott.dmp OWNER=scott

To export specific tables to disk:

- Login to server which has an Oracle client

exp SYSTEM/password FIlE=expdat.dmp TABLES=(scott.emp,hr.countries)

-the above command uses two users : scott and hr

exp / FILE=scott.dmp TABLES=(emp,dept)

the above is only for one user

Using imp:

To import the full database exported in the example above.

imp SYSTEM/password FULL=y FIlE=dba.dmp

To import just the dept and emp tables from the scott schema

imp SYSTEM/password FIlE=dba.dmp FROMUSER=scott TABLES=(dept,emp)

To import tables and change the owner

imp SYSTEM/password FROMUSER=blake TOUSER=scott FILE=blake.dmp TABLES=(unit,manager)

To import just the scott schema exported in the example above

imp / FIlE=scott.dmp