DATA PUMP

Datapump in Oracle
------------------

Oracle Data Pump is a newer, faster and more flexible alternative to the "exp" and "imp" utilities used in previous Oracle versions.
The Oracle Data Pump is a feature of Oracle 10g and later databases that enables very fast bulk data and metadata movement between Oracle databases.
The Oracle Data Pump provides new high-speed, parallel Export and Import utilities (expdp and impdp).
Data Pump Export and Import utilities are faster than the original Export and Import Utilities.
A single thread of Data Pump Export is about twice as fast as original Export, while Data Pump Import is several times faster than original Import.

The list of features for both the Data Pump import and export can be displayed using the HELP parameter.
expdp system/password help=y

Requirements
------------

Create Directory Object
-----------------------
CREATE OR REPLACE DIRECTORY test_dir AS '/u01/app/oracle/oradata/';
GRANT READ, WRITE ON DIRECTORY TO ;

Ensure Oracle user performing Data Pump operation has adequate permissions.
It is common to include a meaningful job name for all Data Pump operations so as a job can be ideally managed.

Export PUMP
------------

expdp user_name/password
  DIRECTORY=dir_obj_name
  DUMPFILE=dump_file_name
  FULL=y|n
  JOB_NAME=job_name
  [COMPRESSION=type]
 
 
expdp system/password DUMPFILE=db1.dmp DIRECTORY=datapump  FULL=y JOB_NAME=expDB LOGFILE=exportDatabase.log

Import
------

impdp user_name/password
  DIRECTORY=dir_obj_name
  DUMPFILE=dump_file_name
  FULL=y|n
  JOB_NAME=job_name

  impdp system/password DUMPFILE=db1.dmp DIRECTORY=datapump FULL=y  JOB_NAME=impDB LOGFILE=impDatabase.log

  Parameter File (.par)
  ---------------------
 
Export
------
# expdp system/password PARFILE=expDatabase.par COMPRESSION=ALL
DIRECTORY=datapump
DUMPFILE=db1.dmp
FULL=Y
JOB_NAME=expDB
LOGFILE=expDatabase.log

Import
------

# impdp system/password PARFILE=impDatabase.par
DIRECTORY=datapump
DUMPFILE=db1.dmp
FULL=Y
JOB_NAME=impDB
LOGFILE=impDatabase.log
TABLE_EXISTS_ACTION=replace



Killing a Data Pump Job
----------------------

Standard Method
--------------
Determine the active Data Pump job name.

Attach to the Data Pump console using the identified jobname.

Issue the command to kill job.

SQL> SELECT j.owner_name, j.job_name,
            j.job_mode, j.state, s.session_type, s.saddr
     FROM dba_datapump_jobs j,dba_datapump_sessions s
     WHERE UPPER(j.job_name) = UPPER(s.job_name);

Owner     JOB_NAME        JOB_MODE     STATE        SESSION_TYPE    SADDR
--------- --------------- ------------ ------------ --------------- --------
SYSTEM    xSCHEMA         SCHEMA       EXECUTING    WORKER          AD09C2D8
SYSTEM    xSCHEMA         SCHEMA       EXECUTING    MASTER          AD09E8A8
SYSTEM    xSCHEMA         SCHEMA       EXECUTING    DBMS_DATAPUMP   AD0BFA08

OS> expdp system/password attach=EXPORTSCHEMA
Export> kill_job
Export> Are you sure you wish to stop this job ([yes]/no): yes

If this is an import then use impdp correspondingly.

Advanced Method
---------------
If the standard method does not work and the job is no longer active.
Log into SQLPlus as SYS.
Drop the corresponding job table.
DROP TABLE .;

OS> sqlplus sys/password as sysdba

SQL> DROP TABLE SYSTEM.EXPORTSCHEMA;