Changing the Parallelism of a Running Oracle Data Pump Job

It is late at night, an export has been running for two hours, and it is clearly going to miss the window. It was started with PARALLEL=1, or with none at all. Do you kill it and start again?

No. Oracle Data Pump lets you attach to a running job and change its parallelism on the fly, without losing the work already done. This post shows how, plus the one extra step that decides whether it actually gets faster, and the commands for monitoring, stopping and cleaning up jobs.

Applies to: Oracle 11.2 through 19c and later. Examples are from 19c.

1.Find the running job

SQL*Plus

SQL> COL owner_name FOR a12
SQL> COL job_name   FOR a28
SQL> SELECT owner_name, job_name, operation, job_mode, state, degree, attached_sessions
     FROM   dba_datapump_jobs
     ORDER  BY owner_name, job_name;

OWNER_NAME   JOB_NAME                     OPERATION  JOB_MODE  STATE      DEGREE  ATTACHED_SESSIONS
------------ ---------------------------- ---------- --------- ---------- ------  -----------------
SYSTEM       SYS_EXPORT_SCHEMA_01         EXPORT     SCHEMA    EXECUTING       1                  1

Unless JOB_NAME was given on the command line, Data Pump names jobs SYS_<operation>_<mode>_NN. DEGREE is the current parallelism.

2.Attach to the job

From any session on the database server, as the job owner or a user with DATAPUMP_EXP_FULL_DATABASE:

shell

expdp system ATTACH=SYSTEM.SYS_EXPORT_SCHEMA_01
# use impdp ... ATTACH=... for an import job

Attaching starts no new work. It just opens the interactive Export> prompt for the running job and prints its status. If you still have the original expdp window open, press Ctrl+C there to get the same prompt; the job keeps running.

Status at attach (abbreviated)

Job: SYS_EXPORT_SCHEMA_01
  Owner: SYSTEM
  Operation: EXPORT
  Mode: SCHEMA
  State: EXECUTING
  Current Parallelism: 1
  Dump File: /u02/dpump/app_exp.dmp
Worker 1 Status:
  Process Name: DW00
  State: EXECUTING
  Object Name: ORDERS
  Object Type: SCHEMA_EXPORT/TABLE/TABLE_DATA

3.Raise parallelism, and give the workers files

This is the step most notes leave out. Every worker writing data needs its own dump file. If the job was started with a single file such as app_exp.dmp, workers queue for that one file and the extra parallelism is mostly wasted. Add files with the %U wildcard before raising PARALLEL:

Export> ADD_FILE=dpump_dir:app_exp_%U.dmp

Adds up to 99 files on demand (app_exp_01.dmp, _02 …). Use %L in 12.2+ if you need more.

Export> PARALLEL=8

Starts more worker processes. Takes effect as soon as there is work for them.

Export> STATUS

Shows the new Current Parallelism and what each worker is doing.

Illustrative output

Export> STATUS

Job: SYS_EXPORT_SCHEMA_01
  Operation: EXPORT
  Mode: SCHEMA
  State: EXECUTING
  Bytes Processed: 21,474,836,480
  Percent Done: 37
  Current Parallelism: 8
  Dump File: /u02/dpump/app_exp.dmp
  Dump File: /u02/dpump/app_exp_01.dmp
  Dump File: /u02/dpump/app_exp_02.dmp
  ...
Worker 1 Status:   DW00  EXECUTING  ORDERS
Worker 2 Status:   DW01  EXECUTING  ORDER_ITEMS
Worker 3 Status:   DW02  EXECUTING  CUSTOMERS
...

Export> STATUS=60

Refreshes the status automatically every 60 seconds.

Export> EXIT_CLIENT

Leaves the prompt. The job keeps running in the background; you can attach again at any time.

Lowering parallelism works too (for example during business hours): PARALLEL=2. Workers finish their current object and then stop, so the change is gradual.

Same change from PL/SQL, which is handy in a script or scheduler job:

PL/SQL

DECLARE
  h NUMBER;
BEGIN
  h := DBMS_DATAPUMP.ATTACH(job_name => 'SYS_EXPORT_SCHEMA_01', job_owner => 'SYSTEM');
  DBMS_DATAPUMP.SET_PARALLEL(handle => h, degree => 8);
  DBMS_DATAPUMP.DETACH(h);
END;
/

4.Monitor progress from SQL

No need to stay attached. These queries work from any SQL session:

SQL*Plus

-- Progress and estimated time remaining (sofar/totalwork are in MB)
SELECT opname, sid, serial#,
       ROUND(sofar/NULLIF(totalwork,0)*100,1) AS pct_done,
       sofar AS mb_done, totalwork AS mb_total,
       ROUND(time_remaining/60) AS min_left
FROM   v$session_longops
WHERE  opname IN (SELECT job_name FROM dba_datapump_jobs)
AND    sofar <> totalwork;

-- Master and worker sessions, with what they are waiting on
SELECT d.job_name, d.session_type, s.sid, s.program, s.event, s.seconds_in_wait
FROM   dba_datapump_sessions d
JOIN   v$session s ON s.saddr = d.saddr
ORDER  BY d.job_name, d.session_type;

If the workers mostly wait on I/O events, more parallelism will not help: the disk is the limit, not the CPU.

Interactive command reference

Command	Export	Import	What it does
STATUS[=n]	✔	✔	Show job and worker status; repeat every n seconds
PARALLEL=n	✔	✔	Change the number of active workers
ADD_FILE=[dir:]file_%U.dmp	✔		Add dump files (export only)
FILESIZE=size	✔		Maximum size of new dump files, e.g. 10G
STOP_JOB[=IMMEDIATE]	✔	✔	Stop the job cleanly (it can be restarted). IMMEDIATE stops workers at once
START_JOB	✔	✔	Restart a stopped job from where it left off (after attaching)
KILL_JOB	✔	✔	Stop the job and delete it, including the master table. It cannot be restarted
CONTINUE_CLIENT	✔	✔	Go back to log mode (job output scrolls again)
EXIT_CLIENT	✔	✔	Leave the client; the job keeps running
HELP	✔	✔	List all interactive commands

STOP_JOB vs KILL_JOB: STOP_JOB keeps the master table, so the job can be resumed with START_JOB. KILL_JOB throws all of that away. Use it only when you really want to abandon the job.

Clean up orphaned jobs

A job that crashed or was killed at the OS level can stay in dba_datapump_jobs as NOT RUNNING, together with its master table, which has the same name as the job:

SQL*Plus

SELECT owner_name, job_name, state, attached_sessions
FROM   dba_datapump_jobs
WHERE  state = 'NOT RUNNING' AND attached_sessions = 0;

First try to attach and KILL_JOB. If that fails and you are sure the job is not needed, drop its master table:

SQL*Plus

DROP TABLE system.sys_export_schema_01 PURGE;

Check first: a stopped job (STOP_JOB) also shows NOT RUNNING. If someone plans to restart it, dropping the master table destroys that option.

Get it right from the start

Changing parallelism mid-job is a rescue. For the next run, start with a parameter file like this:

app_exp.par

DIRECTORY=dpump_dir
DUMPFILE=app_exp_%U.dmp
FILESIZE=20G
LOGFILE=app_exp.log
SCHEMAS=APP
PARALLEL=8
EXCLUDE=STATISTICS
METRICS=YES
LOGTIME=ALL
JOB_NAME=APP_EXP_NIGHTLY

shell

expdp system PARFILE=app_exp.par

• PARALLEL: a good starting point is the number of CPU cores, at most about twice that. Make sure the storage can keep up.
• DUMPFILE with %U: always, whenever PARALLEL is above 1.
• EXCLUDE=STATISTICS: gather fresh statistics after the import instead. It is usually faster and better.
• METRICS=YES, LOGTIME=ALL (12c+): timings per object in the log, so you can see what took the time.
• JOB_NAME: a meaningful name makes it easy to find and attach to the job later.

Things to know

Fact	Detail
Edition	PARALLEL above 1 needs Enterprise Edition. On Standard Edition 2, Data Pump runs with one worker
One big table	Each table is processed by one worker, unless it is partitioned or large enough for Data Pump to unload it with parallel query. Many small tables parallelise better than one huge one
Metadata	From 12.2, metadata is also exported and imported in parallel. Earlier versions handle it with one worker
Import	PARALLEL can be changed during an import too. ADD_FILE does not apply, because the dump files already exist
RAC	Workers can run on all instances. Use CLUSTER=NO if the directory is not on shared storage

Summary

Find the job in dba_datapump_jobs → expdp ATTACH=owner.job → ADD_FILE=..._%U.dmp → PARALLEL=n → STATUS → EXIT_CLIENT. The job keeps all the work it has done, and the new workers join in straight away. Next time, start with %U dump files and a sensible PARALLEL, and you will not need the rescue.

What is the biggest Data Pump job you have had to speed up mid-run? Tell me in the comments.

Tamilselvan Perumal · Oracle ACE Apprentice · Lead Product Engineer & Senior DBA