How To Stop A Running Datapump Job Started In Background (Doc ID 958532.1)

In this Document

APPLIES TO:

Oracle Database - Enterprise Edition - Version 10.1.0.2 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Information in this document applies to any platform.
***Checked for relevance on 09-Apr-2013***

GOAL

The goal of this document is to demonstrate how a running Datapump job can be stopped.

Suppose a session has started a Datapump export operation using the following UNIX command:

$ nohup expdp parfile=test.par &

The example in the document demonstrates how to stop this job from further processing. The same principles apply to jobs initiated using the DBMS_DATAPUMP API.

SOLUTION

To stop the running job the name of the job must first be retrieved. This can be done using:

connect / as sysdba

			select owner_name, job_name from dba_datapump_jobs;

Once the job name is known, that particular job can be stopped by interactively attaching to that job using the ATTACH command line option of Datapump and then stop the job. The following example demonstrates this. The sample assumes that the owner of the job is SCOTT and the job name is TEST, so in this case EXPDP must connect to the SCOTT schema and attach to the TEST job:

$ expdp scott/<PASSWORD> attach=test 

			Export: Release 11.2.0.1.0 - Production on Sat Oct 24 12:47:28 2009

			Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

			Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

			With the Partitioning, OLAP, Data Mining and Real Application Testing options

			Job: TEST

			Owner: TEST

			Operation: EXPORT

			Creator Privs: TRUE

			GUID: 76AA1EE3F300E784E040B10A543B0825

			Start Time: Saturday, 24 October, 2009 12:47:28

			Mode: SCHEMA

			Instance: ORA11GR2

			Max Parallelism: 0

			EXPORT Job Parameters:

			Parameter Name Parameter Value:

			CLIENT_COMMAND scott/<PASSWORD> parfile=test.par

			State: DEFINING

			Bytes Processed: 0

			Job Error Count: 0

			Dump File: /<DIRECTORY_PATH>/test_23oct_rr.dmp

			bytes written: 4,096

			Export>

Once attached to the job interactively you can manipulate the job. In order to stop the job from running, the STOP_JOB command should be issued. When activated, the command asks for comfirmation. The answer "yes" should be provided to really stop the job:

Export> stop_job=immediate 

			Are you sure you wish to stop this job ([yes]/no): yes <<==

The DBA_DATAPUMP_JOBS view can be queried again to see that the job is not running anymore:

SQL> select job_name,state from dba_datapump_jobs;

			JOB_NAME              STATE

			--------------------- -------------------------

			TEST                  NOT RUNNING