Oracle, Web, Script, SQLserver, Tips & Trick

April 7, 2011

Examples of Using Data Pump Export

Filed under: oracle,oracle:10g — sikathabis @ 7:00 pm
Tags: ,

Performing a Table-Mode Export

Example 2–1 shows a table-mode export, specified using the TABLES parameter. Issuethe following Data Pump export command to perform a table export of the tablesemployees and jobs from the human resources (hr) schema:

Example 2–1 Performing a Table-Mode Export

expdp hr/hr TABLES=employees,jobs DUMPFILE=dpump_dir1:table.dmp NOLOGFILE=y

Because user hr is exporting tables in his own schema, it is not necessary to specify theschema name for the tables. The NOLOGFILE=y parameter indicates that an Export logfile of the operation will not be generated.

Data-Only Unload of Selected Tables and Rows

Example 2–2 shows the contents of a parameter file (exp.par) that you could use toperform a data-only unload of all tables in the human resources (hr) schema except forthe tables countries and regions. Rows in the employees table are unloaded thathave a department_id other than 50. The rows are ordered by employee_id.

Example 2–2 Data-Only Unload of Selected Tables and Rows

QUERY=employees:"WHERE department_id !=50 ORDER BY employee_id"

You can issue the following command to execute the exp.par parameter file:

> expdp hr/hr PARFILE=exp.par

A schema-mode export (the default mode) is performed, but the CONTENT parameter effectively limits the export to an unload of just the table’s data. The DBA previously created the directory object dpump_dir1 which points to the directory on the server where user hr is authorized to read and write export dump files. The dump filedataonly.dmp is created in dpump_dir1.

Performing a Parallel Full Database Export

Example 2–5 shows a full database Export that will have 3 parallel worker processes.

Example 2–5 Parallel Full Export

> expdp hr/hr FULL=y DUMPFILE=dpump_dir1:full1%U.dmp, dpump_dir2:full2%U.dmpFILESIZE=2G PARALLEL=3 LOGFILE=dpump_dir1:expfull.log JOB_NAME=expfull

Because this is a full database export, all data and metadata in the database will be exported. Dump files full101.dmp, full201.dmp, full102.dmp, and so on will be created in a round-robin fashion in the directories pointed to by the dpump_dir1 anddpump_dir2 directory objects. For best performance, these should be on separate I/Ochannels. Each file will be up to 2 gigabytes in size, as necessary. Initially, up to three files will be created. More files will be created, if needed. The job and master table will have a name of expfull. The log file will be written to expfull.log in the dpump_dir1 directory.

Using Interactive Mode to Stop and Reattach to a Job

To start this example, reexecute the parallel full export in Example 2–5. While the export is running, press Ctrl+C. This will start the interactive-command interface of Data Pump Export. In the interactive interface, logging to the terminal stops and the Export prompt is displayed.

Example 2–6 Stopping and Reattaching to a Job

At the Export prompt, issue the following command to stop the job:

Are you sure you wish to stop this job ([y]/n): y

The job is placed in a stopped state and exits the client.

Enter the following command to reattach to the job you just stopped:

> expdp hr/hr ATTACH=EXPFULL

After the job status is displayed, you can issue the CONTINUE_CLIENT command toresume logging mode and restart the expfull job.


A message is displayed that the job has been reopened, and processing status is outputto the client.


taken from Oracle Database Utilities 10g Release 2.pdf page 92 until 94

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

Blog at



Learning is not a spectator sport

Connor McDonald's Oracle blog

Teknologi Pendidikan

Just another weblog


Jurnalisme Yang Dapat Dipercaya

Qey050489's Blog

Ayo Bangkit sobat..!!!!!

B. S. Totoraharjo

Orang Desa Yang Mencintai Desa dan Kebudayaannya

culturekitchen foodlab

New Food and Art in Yogyakarta

Wahyu Catur Wibowo

Musafir Dunia Maya

SQL.... Still Learning

My Reference on SQL Server


"A distance is getting far if your heart is impatient" -ean-


Street Photography is like a box of a chocolates, you're never know what you're gonna get - Thomas Leuthard -

Keluarga Andrivito

-- Andrivito's Journal --


This site is about Latest NEWS, Articles, Videos, Books, Course, Presentations, Tips & Tricks related to I.T Pro's

Gaurav Saxena

|| तत् त्वम् असि ||

Be a good Moeslim

Menuju Pribadi Muslim Sejati yang Bertaqwa


Manajemen Sholat, renungan spiritual, Teknologi Informasi

Pelangi di khatulistiwa....

Mari berbagi kebaikan, pengalaman dan senyum denganku


Ceritanya saya, Pikirannya saya, Hatinya saya

%d bloggers like this: