Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

        
 

 Oracle Data Pump Export (EXPDP)
Oracle Tips by Burleson Consulting

Advanced Oracle Utilities: The Definitive Reference by Rampant TechPress is written by four of the top Oracle database experts (Bert Scalzo, Donald Burleson, and Steve Callan).  The following is an excerpt from the book.

Data Pump Export (EXPDP) is a very modern, server based and highly scalable data unloading utility. On typical multi-processor servers with good disk-I/O subsystems, the time to unload hundreds of gigabytes to terabytes is both reliable and reasonable. And even though the dump files remain Oracle proprietary, there are also easily identifiable uses of XML within those files. Thus, uncompressed export files are semi-readable within a text editor, and as before, can be scanned with operating system commands such as string on UNIX.

 

As of Oracle 11g Release 2, the older client based export (i.e. exp) utility will no longer be available or supported. Data Pump Export will become the chief and only method available.

 

A good place to start is by identifying the most frequent data pump export command line parameters:

 

ATTACH

[SCHEMA.]JOB_NAME

Name of an already existing and executing job to connect to. Need EXP_FULL_DATABASE privilege for other schemas

COMPRESSION

META_DATA_ONLY | ALL | NONE | DATA_ONLY

Compress the dump file contents for the specified criteria

CONTENT

ALL | META_DATA_ONLY | DATA_ONLY

Filter the export of dump file contents to the specified criteria

DIRECTORY

DATA_PUMP_DIR | DIRECTORY_NAME

Name of directory object pointing to a valid server directory

DUMPFILE

[DIRECTORY_NAME:]FILE_NAME [, ...]

The name (and optionally the directory) of the export data file

ESTIMATE

N | Y

Do not export, but rather just estimate the disk space required

ESTIMATE_ONLY

BLOCKS, STATISTICS

Method export uses to calculate the disk space for data only

EXCLUDE

OBJECT_TYPE[:NAME_FILTER_EXPRESSION] [, ...]

Database object types as a whole or by object name filter to specifically exclude from the export

FILESIZE

INTEGER [B | K | M | G]

The maximum file size permitted for any export dump file

FULL

N | Y

Whether to perform a full database export or not

Requires EXP_FULL_DATABASE privilege

INCLUDE

OBJECT_TYPE[:NAME_FILTER_EXPRESSION] [, ...]

Database object types as a whole or by object name filter to specifically include in the export

JOB_NAME

SYS_EXPORT_<mode>_NN | JOB_NAME

Name by which export job can be referenced (e.g. ATTACH)

LOGFILE

EXPORT.LOG | [DIRECTORY_NAME:]FILE_NAME

The name (and optionally the directory) of the export log file

NOLOGFILE

N | Y

Whether or not to suppress creation of the export log file

PARALLEL

1 | INTEGER

The maximum number of concurrent threads for the export

PARFILE

[DIRECTORY_SPECIFICATION]FILE_NAME

Name of the operating system specific parameter file

QUERY

[[SCHEMA.]TABLE_NAME:] FILTER_EXPRESSION

Data filter condition applied to all tables or by schema and object name filters during the export

REUSE_DUMPFILES

N | Y

Whether or not to overwrite pre-existing export dump files

SAMPLE

[[SCHEMA.]TABLE_NAME]N, where .000001>=N<100

Probability that a data block of rows will be included in the export’s sampling of the data (i.e. subset)

SCHEMAS

SCHEMA [, ...]

The schema or schemas to export

Need EXP_FULL_DATABASE privilege for other schemas

STATUS

0 | INTEGER

The frequency in seconds which job displays client feedback

TABLES

[SCHEMA.]TABLE_NAME[:PARTITION_NAME] [, ...]

List of tables for a table mode database export

Restricted to a single schema

TABLESPACES

TABLESPACE_NAME  [, ...]

List of tablespaces for a tablespace mode database export

TRANSPORT_FULL_CHECK

N | Y

Whether or not dependencies verified between transportable tablespace objects in the transport set to those outside the set

TRANSPORT_TABLESAPCES

TABLESPACE_NAME  [, ...]

List of tablespaces for a transportable tablespace mode database export (target database version >= source version)

Table 6.1:  Frequent EXPDP Command Line Parameters

Moreover, to use the stop and restart data pump job capabilities, run data pump in interactive mode so as to get the data pump prompt. Then the following commands are also quite frequently useful:

 

CONTINUE_CLIENT

Connect client to currently executing job or restarts the job, and resumes logging mode (i.e. status output)

EXIT_CLIENT

Disconnect client connection to currently executing job and terminate the client process, but leave server job running

KILL_JOB

Detach all client processes connected to this data pump job and then terminate (i.e. kill) the currently running job

START_JOB

Start or resume the current data pump job

STOP_JOB

[IMMEDIATE]

Detach all client processes connected to this data pump job and then orderly shutdown the currently running job

Table 6.2:  Additional EXPDP Parameters

So examine some very common use cases and how data pump export would be used to extract the data.

     

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.