How Does the Client Interface to the Data Pump API Work?

The main structure used in the client interface is a job handle, which appears to the caller as an integer. Handles are created using the DBMS_DATAPUMP.OPEN or DBMS_DATAPUMP.ATTACH function. Other sessions can attach to a job to monitor and control its progress. This allows a DBA to start up a job before departing from work and then watch the progress of the job from home. Handles are session specific. The same job can create different handles in different sessions.

Job States

There is a state associated with each phase of a job, as follows:

  • Undefined - before a handle is created

  • Defining - when the handle is first created

  • Executing - when the DBMS_DATAPUMP.START_JOB procedure is executed

  • Completing - when the job has finished its work and the Data Pump processes are ending

  • Completed - when the job is completed

  • Stop Pending - when an orderly job shutdown has been requested

  • Stopping - when the job is stopping

  • Idling - the period between the time that a DBMS_DATAPUMP.ATTACH is executed to attach to a stopped job and the time that a DBMS_DATAPUMP.START_JOB is executed to restart that job

  • Not Running - when a master table exists for a job that is not running (has no Data Pump processes associated with it)

Performing DBMS_DATAPUMP.START_JOB on a job in an Idling state will return it to an Executing state.

If all users execute DBMS_DATAPUMP.DETACH to detach from a job in the Defining state, then the job will be totally removed from the database.

When a job abnormally terminates or when an instance running the job is shut down, the job is placed in the Not Running state if it was previously executing or idling. It can then be restarted by the user.

The master control process is active in the Defining, Idling, Executing, Stopping, Stop Pending, and Completing states. It is also active briefly in the Stopped and Completed states. The master table for the job exists in all states except the Undefined state. Worker processes are only active in the Executing and Stop Pending states, and briefly in the Defining state for import jobs.

Detaching while a job is in the Executing state will not halt the job, and you can re-attach to an executing job at any time to resume obtaining status information about the job.

A Detach can occur explicitly, when the DBMS_DATAPUMP.DETACH procedure is executed, or it can occur implicitly when a Data Pump API session is run down, when the Data Pump API is unable to communicate with a Data Pump job, or when the DBMS_DATAPUMP.STOP_JOB procedure is executed.

The Not Running state indicates that a master table exists outside the context of an executing job. This will occur if a job has been stopped (probably to be restarted later) or if a job has abnormally terminated. This state can also be seen momentarily during job state transitions at the beginning of a job, and at the end of a job before the master table is dropped. Note that the Not Running state is shown only in the DBA_DATAPUMP_JOBS view and the USER_DATAPUMP_JOBS view. It is never returned by the GET_STATUS procedure.

Table 6-1 shows the valid job states in which DBMS_DATAPUMP procedures can be executed. The states listed are valid for both export and import jobs, unless otherwise noted.

Table 6-1 Valid Job States in Which DBMS_DATAPUMP Procedures Can Be Executed

Procedure Name Valid States Description

Defining (valid for both export and import jobs)

Executing and Idling (valid only for specifying dump files for export jobs)

Specifies a file for the dump file set, the log file, or the SQLFILE output.


Defining, Executing, Idling, Stopped, Completed, Completing, Not Running

Allows a user session to monitor a job or to restart a stopped job. The attach will fail if the dump file set or master table for the job have been deleted or altered in any way.



Restricts data processed by a job.



Disconnects a user session from a job.



Retrieves dump file header information.


All, except Completed, Not Running, Stopped, and Undefined

Obtains the status of a job.


Defining, Executing, Idling, Stop Pending, Completing

Adds an entry to the log file.



Restricts metadata processed by a job.



Remaps metadata processed by a job.



Alters metadata processed by a job.



Creates a new job.


Defining, Executing, Idling

Specifies parallelism for a job.


DefiningFoot 1

Alters default processing by a job.


Defining, Idling

Begins or resumes execution of a job.


Defining, Executing, Idling, Stop Pending

Initiates shutdown of a job.


All, except Completed, Not Running, Stopped, and Undefined

Waits for a job to end.

Footnote 1

The ENCRYPTION_PASSWORD parameter can be entered during the Idling state, as well as during the Defining state.