datapump 10g

November 18, 2009 at 1:27 pm (Oracle) (datapump, oracle datapump)

Connect
CONN sys/password@db10g AS SYSDBA
ALTER USER scott IDENTIFIED BY tiger ACCOUNT UNLOCK;
GRANT CREATE ANY DIRECTORY TO scott;

CREATE OR REPLACE DIRECTORY backup_dir AS ‘/u01/app/oracle/oradata/’;
GRANT READ, WRITE ON DIRECTORY backup_dir TO scott;

Table Exports/Imports

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

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

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

Schema Exports/Imports

OWNER parameter of exp has been replaced by the SCHEMAS parameter for schemas export.

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

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

For example output files see expdpSCOTT.log and 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=BACKUP_DIR dumpfile=DB10G.dmp logfile=expdpDB10G.log

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

For an example output file see expdpDB10G.log.

Miscellaneous Information
Unlike the original exp and imp utilities all data pump “.dmp” and “.log” files are created on the Oracle server, not the client machine.

All data pump actions are performed by multiple jobs (server processes not DBMS_JOB jobs). These jobs are controlled by a master control process which uses Advanced Queuing. At runtime an advanced queue table, named after the job name, is created and used by the master control process. The table is dropped on completion of the data pump job. The job and the advanced queue can be named using the JOB_NAME parameter. Cancelling the client process does not stop the associated data pump job. Issuing “ctrl+c” on the client during a job stops the client output and presents a command prompt. Typing “status” at this prompt allows you to monitor the current job.

Export> status

system@db10g> select * from dba_datapump_jobs;

INCLUDE and EXCLUDE

The INCLUDE and EXCLUDE parameters can be used to limit the export/import to specific objects. When the INCLUDE parameter is used, only those objects specified by it will be included in the export. When the EXCLUDE parameter is used all objects except those specified by it will be included in the export.

expdp scott/tiger@db10g schemas=SCOTT include=TABLE:”IN (‘EMP’, ‘DEPT’)” directory=BACKUP_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log

expdp scott/tiger@db10g schemas=SCOTT exclude=TABLE:”= ‘BONUS’” directory=BACKUP_DIR dumpfile=SCOTT.dmp logfile=expdpSCOTT.log

Data Pump API
Oracle provide PL/SQL API for use with datapump utilities.

Help
The HELP=Y option displays the available parameters.

expdp help=y