DataPump Internals

Sample Parameter file

DIRECTORY=DATA_PUMP_DIR
DUMPFILE=abc.dmp
LOGFILE=abc.log
FULL=Y
EXCLUDE=STATISTICS
EXCLUDE=TABLE:"IN ('NAME', 'ADDRESS' , 'EMPLOYEE' , 'DEPT')"
EXCLUDE=SCHEMA:"IN ('WMSYS', 'OUTLN')"

COMMAND LINE SYNTAX for EXPDP

expdp system/password DIRECTORY=DATA_PUMP_DIR DUMPFILE=abc.dmp FULL=Y
EXCLUDE=TABLE:\"IN \(\'NAME\', \'ADDRESS\' , \'EMPLOYEE\' , \'DEPT\'\)\"
EXCLUDE=SCHEMA:\"IN \(\'WMSYS\', \'OUTLN\'\)\"

WE CAN BENIFIT FROM FILESIZE PARAMETER BY CREATING MULTIPLE FILES IF WE ARE USING PARALLEL=Y OPTION.Without creating Multiple files you cannot benefit from paralellism.

If your dump size is 30G approximately and you use PARALLEL=5 then you should add filesize=7G so it can create 5 equal size files which are later easy to be manage.

If you are running parallel export on RAC then you must add cluster=N command otherwise, you will get "File not found error" because worker processes which are working under master process of datapump will connect to both nodes if CLUSTER=N will not be used and as files are on one node any worker process running from second node will throw exception.


expdp 'sys/oracle@rctest1 as sysdba' DIRECTORY=DMP_DIR DUMPFILE=full_RCTEST%U.dmp PARALLEL=5 FILESIZE=7G FULL=Y CLUSTER=N


##############################
ADDING DATE
##############################

expdp ' / as sysdba' DIRECTORY=DMPDIR_X FULL=Y cluster=N parallel=6 dumpfile=FINDBPRD2_%U_%date:~7,2%%date:~4,2%%date:~10,4%.dmp ogfile=findbprd2_%date:~7,2%%date:~4,2%%date:~10,4%.log filesize=20G EXCLUDE=STATISTICS
impdp hr@inst1 DIRECTORY=dpump_dir1 DUMPFILE=hr.dmp TABLES=employees

Data Filters

Specifying Import Parameters

TABLES=employees,jobs or TABLES=employees jobs

For every parameter you enter, you must enter an equal sign (=) and a value. Data Pump has no other way of knowing that the previous parameter specification is complete and a new parameter specification is beginning. For example, in the following command line, even though NOLOGFILE is a valid parameter, it would be interpreted as another dump file name for the DUMPFILE parameter:

impdp DIRECTORY=dpumpdir DUMPFILE=test.dmp NOLOGFILE TABLES=employees

This would result in two dump files being created, test.dmp and nologfile.dmp.

To avoid this, specify either NOLOGFILE=YES or NOLOGFILE=NO.

ACCESS_METHOD
Default: AUTOMATIC

Purpose

Instructs Import to use a particular method to load data.

Syntax and Description

ACCESS_METHOD=[AUTOMATIC | DIRECT_PATH | EXTERNAL_TABLE | CONVENTIONAL

Example

> impdp hr SCHEMAS=hr DIRECTORY=dpump_dir1 LOGFILE=schemas.log
DUMPFILE=expdat.dmp ACCESS_METHOD=CONVENTIONAL 


#################################
CONTENT
#################################

CONTENT=[ALL | DATA_ONLY | METADATA_ONLY]
ALL loads any data and metadata contained in the source. This is the default.

DATA_ONLY loads only table row data into existing tables; no database objects are created.

METADATA_ONLY loads only database object definitions; no table row data is loaded. Be aware that if you specify CONTENT=METADATA_ONLY, then any index or table statistics imported from the dump file are locked after the import operation is complete.

impdp hr DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp CONTENT=METADATA_ONLY

################################
EXCLUDE
################################
EXCLUDE=FUNCTION
EXCLUDE=PROCEDURE
EXCLUDE=PACKAGE
EXCLUDE=INDEX:"LIKE 'EMP%' "

###############################
FLASHBACK SCN
###############################

FLASHBACK_TIME="TO_TIMESTAMP('25-08-2008 14:35:00', 'DD-MM-YYYY HH24:MI:SS')"

###############################
INCLUDE
###############################

INCLUDE=FUNCTION
INCLUDE=PROCEDURE
INCLUDE=PACKAGE
INCLUDE=INDEX:"LIKE 'EMP%' "
You can then issue the following command:

> impdp system SCHEMAS=hr DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp 
PARFILE=imp_include.par


################################
JOB NAME
################################

Purpose

The job name is used to identify the import job in subsequent actions, such as when the ATTACH parameter is used to attach to a job, or to identify the job via the DBA_DATAPUMP_JOBS or USER_DATAPUMP_JOBS views.

Syntax and Description

JOB_NAME=jobname_string
The jobname_string specifies a name of up to 30 bytes for this import job. The bytes must represent printable characters and spaces. If spaces are included, then the name must be enclosed in single quotation marks (for example, 'Thursday Import'). The job name is implicitly qualified by the schema of the user performing the import operation. The job name is used as the name of the master table, which controls the export job.

The default job name is system-generated in the form SYS_IMPORT_mode_NN or SYS_SQLFILE_mode_NN, where NN expands to a 2-digit incrementing integer starting at 01. An example of a default name is 'SYS_IMPORT_TABLESPACE_02'.

Example

The following is an example of using the JOB_NAME parameter. You can create the expfull.dmp dump file used in this example by running the example provided for the Export FULL parameter. See "FULL".

impdp hr DIRECTORY=dpump_dir1 DUMPFILE=expfull.dmp JOB_NAME=impjob01

#################################
PAR_FILE
#################################

PARFILE
Default: There is no default

Purpose

Specifies the name of an import parameter file.

Syntax and Description

PARFILE=[directory_path]file_name
Unlike dump files, log files, and SQL files which are created and written by the server, the parameter file is opened and read by the impdp client. Therefore, a directory object name is neither required nor appropriate. The default is the user's current directory. The use of parameter files is highly recommended if you are using parameters whose values require the use of quotation marks.

See Also:
"Use of Quotation Marks On the Data Pump Command Line"
Restrictions

The PARFILE parameter cannot be specified within a parameter file.
Example

The content of an example parameter file, hr_imp.par, might be as follows:

TABLES= countries, locations, regions
DUMPFILE=dpump_dir2:exp1.dmp,exp2%U.dmp
DIRECTORY=dpump_dir1
PARALLEL=3

#################################
KILLING A JOB
#################################


How to kill, cancel, restart, stop data pump jobs

Datapump jobs can be stopped in two ways, either press CTRL + C you will get EXPDP prompt or attach to a already running job and after that write STOP_JOB

If datapump job is not running in background then once you press CTRL+C you will get EXPDP prompt . PFB :-

Export> STOP_JOB
Are you sure you wish to stop this job ([yes]/no): y

Datapump job is stopped.

if datapump is running in background then you have to attach a datapump job, find out the datapump job name by using below query :-

SQL> SELECT owner_name, job_name, operation, job_mode, state FROM dba_datapump_jobs;
OWNER_NAME                     JOB_NAME                       OPERATION                           JOB_MODE                  STATE
------------------------------ ------------------------------ ----------------------------------- ------------------------- -----------------------------
SYSTEM                         SYS_EXPORT_FULL_02             EXPORT                              FULL                      EXECUTING

Now attach to a running job:-

C:\Users\NewAdmin>expdp system/orcl attach=SYS_EXPORT_FULL_02

Export: Release 11.2.0.1.0 - Production on Sat Jun 29 20:01:45 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Job: SYS_EXPORT_FULL_02
  Owner: SYSTEM
  Operation: EXPORT
  Creator Privs: TRUE
  GUID: 42902560C22D46BDA6924724126F59FD
  Start Time: Saturday, 29 June, 2013 20:01:08
  Mode: FULL
  Instance: orcl
  Max Parallelism: 1
  EXPORT Job Parameters:
  Parameter Name      Parameter Value:
     CLIENT_COMMAND        system/******** directory=datapump dumpfile=expdp_full_29062013_1.dmp logfile=expdp_full_29062013_1.log compression=all full=y
     COMPRESSION           ALL
  State: EXECUTING
  Bytes Processed: 0
  Current Parallelism: 1
  Job Error Count: 0
  Dump File: C:\DATAPUMP\EXPDP_FULL_29062013_1.DMP
    bytes written: 4,096

Worker 1 Status:
  Process Name: DW00
  State: EXECUTING

Export> STOP_JOB
Are you sure you wish to stop this job ([yes]/no): y

you will get a message like this :-

Job "SYSTEM"."SYS_EXPORT_FULL_02" stopped by user request at 20:02:01

SQL> SELECT owner_name, job_name, operation, job_mode, state FROM dba_datapump_jobs;
OWNER_NAME                     JOB_NAME                       OPERATION                           JOB_MODE                  STATE
------------------------------ ------------------------------ ----------------------------------- ------------------------- ------------------------------
SYSTEM                         SYS_EXPORT_FULL_02             EXPORT                              FULL                      NOT RUNNING

Restart a Stopped datapump Job :-

Attach to a stopped datapump job :-

C:\Users\NewAdmin>expdp system/orcl attach=SYS_EXPORT_FULL_02

Export: Release 11.2.0.1.0 - Production on Sat Jun 29 20:08:17 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Job: SYS_EXPORT_FULL_02
  Owner: SYSTEM
  Operation: EXPORT
  Creator Privs: TRUE
  GUID: 42902560C22D46BDA6924724126F59FD
  Start Time: Saturday, 29 June, 2013 20:08:20
  Mode: FULL
  Instance: orcl
  Max Parallelism: 1
  EXPORT Job Parameters:
  Parameter Name      Parameter Value:
     CLIENT_COMMAND        system/******** directory=datapump dumpfile=expdp_full_29062013_1.dmp logfile=expdp_full_29062013_1.log compression=all full=y
     COMPRESSION           ALL
  State: IDLING
  Bytes Processed: 0
  Current Parallelism: 1
  Job Error Count: 0
  Dump File: C:\datapump\expdp_full_29062013_1.dmp
    bytes written: 4,096

Worker 1 Status:
  Process Name: DW00
  State: UNDEFINED

Export> START_JOB

Export> CONTINUE_CLIENT
Job SYS_EXPORT_FULL_02 has been reopened at Saturday, 29 June, 2013 20:08
Restarting "SYSTEM"."SYS_EXPORT_FULL_02":  system/******** directory=datapump dumpfile=expdp_full_29062013_1.dmp logfile=expdp_full_29062013_1.log compression=all full=y
Processing object type DATABASE_EXPORT/TABLESPACE

Job is again restarted.

Kill a datapump job :-

Attach to a running datapump job :-

C:\Users\NewAdmin>expdp system/orcl attach=SYS_EXPORT_FULL_01

Export: Release 11.2.0.1.0 - Production on Sat Jun 29 20:12:55 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Job: SYS_EXPORT_FULL_01
  Owner: SYSTEM
  Operation: EXPORT
  Creator Privs: TRUE
  GUID: 452263D9B00047A9BCD6E97DE83878F4
  Start Time: Saturday, 29 June, 2013 20:12:57
  Mode: FULL
  Instance: orcl
  Max Parallelism: 1
  EXPORT Job Parameters:
  Parameter Name      Parameter Value:
     CLIENT_COMMAND        system/******** directory=datapump dumpfile=expdp_full_29062013.dmp logfile=expdp_full_29062013.log compression=all full=y
     COMPRESSION           ALL
  State: IDLING
  Bytes Processed: 0
  Current Parallelism: 1
  Job Error Count: 0
  Dump File: C:\datapump\expdp_full_29062013.dmp
    bytes written: 5,570,560

Worker 1 Status:
  Process Name: DW00
  State: RUNNING

Export> KILL_JOB
Are you sure you wish to stop this job ([yes]/no): y

DIfference between KILLING and STOPPING a datapump job is that, once you killed a datapump job you cannot able to restart it . But if we stop a datapump job we can restart it. Killing a datapump job will delete the dump and log files also.


Some other useful links

http://www.davidghedini.com/pg/entry/bash_script_for_oracle_data

http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_import.htm