How to Kill Oracle Export/Import Jobs
In this blog post, we’ll explore various methods to check and terminate Oracle Data Pump export (expdp) and import (impdp) jobs. Oracle provides several ways to kill or manage Data Pump export (expdp) and import (impdp) jobs. Below are the key methods you can use:
1. Using DBMS_DATAPUMP
Oracle provides the DBMS_DATAPUMP package to manage Data Pump jobs. You can stop (or “kill”) a job programmatically using this method.
Identify the Job Name and Job Handle
You first need to identify the job’s handle. If you’ve already created the Data Pump job, you can retrieve its handle by querying the DBA_DATAPUMP_JOBS view.
SQL> desc DBA_DATAPUMP_JOBS
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER_NAME VARCHAR2(128)
JOB_NAME VARCHAR2(128)
OPERATION VARCHAR2(128)
JOB_MODE VARCHAR2(128)
STATE VARCHAR2(128)
DEGREE NUMBER
ATTACHED_SESSIONS NUMBER
DATAPUMP_SESSIONS NUMBERSELECT job_name, job_mode, operation, state, job_id
FROM DBA_DATAPUMP_JOBS
WHERE job_name = '<job_name>';Replace <strong><job_name></strong> with the name of your Data Pump job.
Stop the Job Using DBMS_DATAPUMP:
BEGIN
DBMS_DATAPUMP.detach(handle => '<job_handle>');
END;
/
-- Or to stop a job completely:
BEGIN
DBMS_DATAPUMP.stop_job(job_handle => '<job_handle>');
END;
/This will stop the job immediately. You can also use stop_job to stop the job without detaching it, depending on your needs.
2. Killing the Job Using Oracle Session Management
You can also kill the export/import jobs by terminating the underlying session. Data Pump jobs are executed by Oracle sessions, so identifying and killing the session associated with the job is a quick way to stop the process.
Find the Session ID:
You need to find the session associated with the Data Pump job. You can do this by querying the v$session view:
SELECT s.sid, s.serial#, s.username, s.program
FROM v$session s
WHERE s.username = 'SYS'
AND s.program LIKE 'expdp%' OR s.program LIKE 'impdp%';This query helps you identify sessions related to expdp or impdp.
Kill the Session
ALTER SYSTEM KILL SESSION '<sid>,<serial#>' immediate;Replace and with the actual values from the previous query. This will immediately terminate the session and stop the Data Pump job.
3. Killing the Job Using ps and kill (Linux/Unix)
In a Linux or Unix environment, you can find the OS-level process related to the expdp or impdp job and kill it directly. This method is more appropriate if the above methods don’t work or you cannot locate the session in the database.
Find the Process ID (PID):
You can use the ps command to find the process associated with expdp or impdp:
ps -ef | grep expdp
ps -ef | grep impdpKill the Process:
kill -9 <pid>Replace with the actual process ID you obtained. The kill -9 command will forcefully terminate the process.
4. Killing the Job Using Oracle Enterprise Manager (OEM)
If you are using Oracle Enterprise Manager (OEM) to manage your Oracle database, you can kill export/import jobs directly from the OEM interface.
Steps:
- Log in to Oracle Enterprise Manager.
- Navigate to the “Jobs” section.
- Locate the running export/import job.
- Choose the “Kill” option from the job’s context menu.
This is the easiest method for DBAs who prefer a graphical interface over command-line operations.
5. Killing the Job by Detaching with expdp or impdp Command Line
Attach to a running job:
expdp <username>/<password> attach=<job_name>
impdp <username>/<password> attach=<job_name>Example:
expdp '"/ as sysdba"' attach=SYS_EXPORT_SCHEMA01
impdp '"/ as sysdba"' attach=SYS_IMPORT_SCHEMA01Then kill it.
Export > status -- Displays job progress and status
Export > kill -- Terminates the running job immediately
Export > detach -- Detaches from the job, but it continues running
Export > attach -- Reconnects to a running job to manage itThese options give you control over managing and monitoring Data Pump jobs while they are running.
Read More:
