Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

Export expdp command line options

Oracle Tips by Burleson Consulting

June 9, 2012

 

Question:  I am using the Data Pump Utility for an export with expdp, and I want to know the command line differences between the exp and the expdp utilities.

Exp Options

The older export utility command options include:

 

·        buffer – Specifies the size, in bytes, of the buffer used to fetch the rows.  If 0 is specified, only one row is fetched at a time.  This parameter only applies to conventional (non direct) exports.

·        compress – When “Y”, export will mark the table to be loaded as one extent for the import utility.  If “N”, the current storage options defined for the table will be used.  Although this option is only implemented on import, it can only be specified on export. 

·        consistent – [N] Specifies the set transaction read only statement for export, ensuring data consistency.  This option should be set to “Y” if activity is anticipated while the exp command is executing.  If ‘Y’ is set, confirm that there is sufficient undo segment space to avoid the export session getting the ORA-1555 Snapshot too old error.

·        constraints – [Y] Specifies whether table constraints should be exported with table data.

·        direct – [N] Determines whether to use direct or conventional path export.  Direct path exports bypass the SQL command, thereby enhancing performance.

·        feedback – [0] Determines how often feedback is displayed.  A value of feedback=n displays a dot for every n rows processed.  The display shows all tables exported not individual ones.  From the output below, each of the 20 dots represent 50,000 rows, totaling 1 million rows for the table.

About to export specified tables via Direct Path ...

. .
exporting table   
TABLE_WITH_ONE_MILLION_ROWS

....................

1000000 rows exported

 

·        file – The name of the export file. Multiple files can be listed, separated by commas.  When export fills the filesize, it will begin writing to the next file in the list.

·        filesize – The maximum file size, specified in bytes. 

·        flashback_scn – The system change number (SCN) that export uses to enable flashback.

·        flashback_time – Export will discover the SCN that is closest to the specified time.  This SCN is used to enable flashback.  This is the equivalent to the consistent=y argument in the exp utility.  Starting in Oracle 11.2, Oracle introduced a "legacy mode" allowing consistent=y to be used with the expdp command utility.  See these notes on  using flashback time.

·        full – The entire database is exported.

·        grants – [Y] Specifies object grants to export.

·        help – Shows command line options for export.

·        indexes – [Y] Determines whether index definitions are exported.  The index data is never exported.

·        log – The filename used by export to write messages.  The same messages that appear on the screen are written to this file:

 

Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

 

About to export specified tables via Direct Path ...

. . exporting table    TABLE_WITH_ONE_MILLION_ROWS    1000000 rows exported

Export terminated successfully without warnings.

 

·        object_consistent – [N] Specifies whether export uses SET TRANSACTION READ ONLY to ensure that the data being exported is consistent. 

·        owner – Only the owner’s objects will be exported.

·        parfile – The name of the file that contains the export parameter options.  This file can be used instead of specifying all the options on the command line for each export.

·        query – Allows a subset of rows from a table to be exported, based on a SQL where clause (discussed later in this chapter).   

·        recordlength – Specifies the length of the file record in bytes.  This parameter affects the amount of data that accumulates before it is written to disk.  If not specified, this parameter defaults to the value specific to that platform.  The highest value is 64KB.   

·        resumable – [N] Enables and disables resumable space allocation.  When “Y”, the parameters resumable_name and resumable_timeout are utilized. 

·        resumable_name – User defined string that helps identify a resumable statement that has been suspended.  This parameter is ignored unless resumable = Y.

·        resumable_timeout – [7200 seconds] The time period in which an export error must be fixed.  This parameter is ignored unless resumable = Y.

·        rows – [Y] Indicates whether or not the table rows should be exported.

·        statistics – [ESTIMATE] Indicates the level of statistics generated when the data is imported.  Other options include COMPUTE and NONE.

·        tables – Indicates that the type of export is table-mode and lists the tables to be exported.  Table partitions and sub partitions can also be specified. 

·        tablespaces – Indicates that the type of export is tablespace-mode, in which all tables assigned to the listed tablespaces will be exported.  This option requires the EXP_FULL_DATABASE role.

·        transport_tablespace – [N] Enables the export of metadata needed for transportable tablespaces.

·        triggers – [Y] Indicates whether triggers defined on export tables will also be exported.

·        tts_full_check – [FALSE] When TRUE, export will verify that when creating a transportable tablespace, a consistent set of objects is exported.

·        userid – Specifies the userid/password of the user performing the export.

·        volsize – Specifies the maximum number of bytes in an export file on each tape volume. 

The functionality of the export utility has been significantly enhanced in recent versions of Oracle.  To check which options are available in any release use:

 

Expdp Options

 

exp help=y

 

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

 

PARAMETER

DESCRIPTION

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

CONSISTENT

Y|N

Starting in release 11.2, this option gives a read consistent backup, just like flachback_time

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

FLASHBACK_TIME

SYSTIMESTAMP | DATE

Allows a read consistent export in expdp.  See these notes on using flashback time.

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)

 

 

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:

 

PARAMETER

DESCRIPTION

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

 

 

 
 
 
Get the Complete
Oracle Utility Information 

The landmark book "Advanced Oracle Utilities The Definitive Reference"  contains over 600 pages of filled with valuable information on Oracle's secret utilities. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.
 

 


 

 

��
 
 
 
 

 
 
 

 
Oracle performance tuning software 
 
oracle dba poster
Oracle Linux poster
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright ? 1996 -  2014

All rights reserved by Burleson

Oracle ? is the registered trademark of Oracle Corporation.