# O AT A

!1UICK2EFERENCE

VE BA
$
FOR$"!SAND$EVELOPERS

RS SE
/ 
RA G
CL
E

/RACLE
5TILITIES
0OCKET2EFERENCE

3ANJAY-ISHRA
,ch01copy.16731 Page 34 Wednesday, November 3, 2004 6:03 PM

expdp
Use expdp to extract data and metadata from an Oracle database
into an operating system file in order to transport that data to
another Oracle database, copy objects and data from one schema
to another, or detect block corruption.

TIP
expdp is a new utility in Oracle Database 10g.

Syntax
expdp [login | sysdba_login] [paramval [paramval...]

login ::= username[/password][@net_service_name]]

sysdba_login ::= \'username/password AS SYSDBA\'

paramval ::= {parameter=one_value

|parameter=(value1[,value2...]) }

parameter ::= {
attach=[schema_name.]expdp_job
|content={ALL | DATA_ONLY | METADATA_ONLY}
|directory=dir_name
|dumpfile=[directory_name:]file_name [, ...]
|estimate={BLOCKS | STATISTICS}
|estimate_only={y|n}
|exclude=object_type[:name_filter] [, ...]
|filesize=fsize[B | K | M | G]
|flashback_scn=fscn
|flashback_time=ftime
|full={y|n}
|help={y|n}
|include=object_type[:name_filter] [, ...]
|job_name=expdp_job
|logfile=[directory_name:]log_name [, ...]
|network_link=source_db_link
|nologfile={y|n}
|parallel=dop

34 | Oracle Utilities Pocket Reference

,ch01copy.16731 Page 35 Wednesday, November 3, 2004 6:03 PM

|parfile=parfile_name
|query=q
|schemas=schema_name [, ...]
|status=interval
|tables=[schema.]table[:partition] [, ...]
|tablespaces=tablespace_name [, ...]
|transport_full_check={y|n}
|transport_tablespaces=tablespace_name [, ...]
|version={COMPATIBLE | LATEST | version_string}

Parameters
attach=[schema_name.]expdp_job
Specifies the name of the (already running) data pump export
the client will be attached to. If you want your client to attach
to a job in a schema other than your own, you must have the
EXP_FULL_DATABASE role. When this parameter is speci-
fied, no other parameter (except the connection string) is
allowed. You’ll see an Export> prompt after you connect.
content={ALL | DATA_ONLY | METADATA_ONLY}
Specifies the type of content for the export. The default value
is ALL, which means that both data and metadata (database
object definitions) are exported.
directory=dir_name
Specifies the name of an Oracle directory object, which in
turn points to the actual directory to which the export dump-
file(s) and the logfile will be written. If you specify a directory
in the dumpfile and logfile parameters, the directory param-
eter is overridden.

TIP
Before you can use expdp, you must have a directory ob-
ject created in the database, and the database user must
have read and write privileges (granted via the SQL grant
command, not at the OS level) on the directory.

dumpfile=[directory_name:]file_name [, ...]
Specifies the name(s) of the export dumpfile(s). The default
value is expdat.dmp. Optionally, you can also specify the
directory.

expdp | 35
,ch01copy.16731 Page 36 Wednesday, November 3, 2004 6:03 PM

estimate={BLOCKS | STATISTICS}
Specifies the method to use to estimate disk space consumed
by the export dumpfiles.
estimate_only={y|n}
Specifies whether to estimate the space that will be consumed
by the export, rather than actually exporting.
exclude=object_type[:name_filter] [, ...]
Specifies the object types for which the metadata will be
excluded from exp. Optionally, you can use an expression to
filter objects based on object names.
filesize=fsize[B | K | M | G]
Specifies the maximum size of the file(s) to be written by exp.
flashback_scn=fscn
Specifies the system change number to which the export will
be consistent. This enables flashback.
flashback_time=ftime
Specifies the timestamp to which the export will be consis-
tent. This is another way to specify the flashback SCN. The
expdp utility finds the SCN closest to the specified timestamp.
full={y|n}
Specifies whether to export in full database mode. You need
to have the EXP_FULL_DATABASE role to use this mode.
help={y|n}
Displays a list of expdp parameters. Does no export.
include=object_type[:name_filter] [, ...]
Specifies the object types for which the metadata will be
included in the export. Optionally, you can use an expression
to filter objects based on object names.
job_name=expdp_job
Specifies a name for the export job. This name can later be
used in the attach parameter to attach a client.
logfile=[directory_name:]log_name [, ...]
Specifies the name of the logfile. The default value is export.log.
Optionally, you can specify a directory object name as well.
network_link=source_db_link
Specifies a database link that is used to export from a remote
database.

36 | Oracle Utilities Pocket Reference

,ch01copy.16731 Page 37 Wednesday, November 3, 2004 6:03 PM

nologfile={y|n}
Specifies whether to suppress creation of a logfile.
parallel=dop
Specifies the degree of parallelism for the export job, i.e., the
number of threads of execution for the export job.
parfile=parfile_name
Specifies the name of a file that contains a list of export
parameters and values.
query=q
Specifies a WHERE clause you can use to export a subset of
the data from a table.
schemas=schema_name [, ...]
Specifies the schemas you want to export. The default value is
your own schema. To be able to export other users’ schemas,
you need the EXP_FULL_DATABASE role.
status=interval
Specifies the periodicity (in seconds) at which to display the
status of the export process.
tables=[schema.]table[:partition] [, ...]
Specifies a list of tables, or partitions if you wish to be that
granular, to export.
tablespaces=tablespace_name [, ...]
Specifies a list of tablespaces to be exported.
transport_full_check={y|n}
Specifies whether expdp will verify that the transportable
tablespaces being exported have no dependency outside the
set of tablespaces being exported.
transport_tablespaces=tablespace_name [, ...]
Specifies the tablespaces for which you want to export trans-
portable tablespace metadata.
version={COMPATIBLE | LATEST | version_string}
Specifies a version number with which the output dumpfile
will be compatible.

Examples
You can start the expdp utility by specifying parameters in one of
two ways: as command-line entries or in a parameter file.

expdp | 37
,ch01copy.16731 Page 38 Wednesday, November 3, 2004 6:03 PM

You can specify all the desired parameters as command-line

entries, as illustrated in the following example:
expdp scott/tiger@sandbox dumpfile=expdp_scott1.dmp
directory=data_pump logfile=expdp_scott.log job_
name=expdp_scott status=100
Alternately, you can specify the parameters in a text file called the
parameter file, and then use the parfile parameter to specify the
name of the parameter file to the expdp command, as illustrated in
the following example:
expdp scott/tiger@sandbox parfile=expdp.par
The contents of the parameter file expdp.par are:
dumpfile=expdp_scott1.dmp
directory=data_pump
logfile=expdp_scott.log
job_name=expdp_scott

Interactive mode
The expdp utility has an interactive mode as well, which can be
used for monitoring and administrative tasks. Start the interactive
mode by either pressing Ctrl-C when the job is running, or by
invoking expdp with the attach parameter (discussed earlier).
When you invoke the interactive mode, the Export> prompt is
displayed, and you can use the following commands:
add_file=[directory_object]file_name [,...]
Adds one or more dumpfiles to the dumpfile set.
continue_client
Changes the export mode from interactive mode to logging
mode and, if the job was stopped, restarts it.
exit_client
Exits the client session and stops logging to the terminal, but
the job continues to run in the background.
help
Displays help information.
kill_job
Kills the client sessions and the export job.

38 | Oracle Utilities Pocket Reference

,ch01copy.16731 Page 39 Wednesday, November 3, 2004 6:03 PM

parallel=dop
Specifies the degree of parallelism for the export job, i.e., the
number of threads of execution for the export job.
start_job
Restarts the stopped job you are currently attached to.
status
Displays the status of the job along with estimated percent
complete, degree of parallelism, and so forth.
stop_job[=IMMEDIATE]
Stops the job you are currently attached to. If the optional
value IMMEDIATE is not specified, it stops after completing the
task it is executing. When you specify IMMEDIATE, it stops
immediately without completing the current task.
The following example illustrates how you can begin an export as
a prelude to invoking the interactive mode.
C:\>expdp scott/tiger dumpfile=expdp_scott1.dmp
directory=data_pump logfile=expdp_scott.log job_
name=expdp_scott
...
Starting "SCOTT"."EXPDP_SCOTT": scott/********
dumpfile=expdp_scott1.dmp directory=data_pump
logfile=expdp_scott.log job_name=expdp_scott
Estimate in progress using BLOCKS method...
At this time, press Ctrl-C to invoke the interactive mode. The
Export> prompt appears, at which you can type the interactive
mode commands, such as stop_job to stop processing the current
expdp job.
Export> stop_job=immediate
Are you sure you wish to stop this job ([y]/n): y
C:\>
You are prompted to confirm that you want to stop the job. After
you press y, you are brought back to the operating system prompt.
Having stopped the job, you can query the data dictionary view
DBA_DATAPUMP_JOBS (using SQL*Plus) to find the status of
all the data pump jobs in the database:
select owner_name, job_name, operation, state

expdp | 39
,ch01copy.16731 Page 40 Wednesday, November 3, 2004 6:03 PM

from dba_datapump_jobs;

OWNER_NAME JOB_NAME OPERATION STATE

----------- ------------- ----------- -------
SCOTT EXPDP_SCOTT EXPORT IDLING
You can attach to the stopped job and restart it:
C:\>expdp scott/tiger attach=expdp_scott

Export> start_job

Export> continue_client
Job EXPDP_SCOTT has been reopened at Saturday, 21
August, 2004 22:25
Restarting "SCOTT"."EXPDP_SCOTT": scott/********
dumpfile=expdp_scott1.dmp directory=data_pump
logfile=expdp_scott.log job_name=expdp_scott
...

40 | Oracle Utilities Pocket Reference