{"id":1492,"date":"2025-01-09T18:02:44","date_gmt":"2025-01-09T18:02:44","guid":{"rendered":"https:\/\/w3buddy.com\/?p=1492"},"modified":"2026-01-15T13:17:26","modified_gmt":"2026-01-15T07:47:26","slug":"how-to-kill-oracle-export-import-jobs","status":"publish","type":"post","link":"https:\/\/w3buddy.com\/blog\/how-to-kill-oracle-export-import-jobs\/","title":{"rendered":"How to Kill Oracle Export\/Import Jobs"},"content":{"rendered":"\n

In this blog post, we\u2019ll 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:<\/p>\n\n\n\n

1. Using DBMS_DATAPUMP<\/h2>\n\n\n\n

Oracle provides the DBMS_DATAPUMP package to manage Data Pump jobs. You can stop (or “kill”) a job programmatically using this method.<\/p>\n\n\n\n

Identify the Job Name and Job Handle<\/h3>\n\n\n\n

You first need to identify the job\u2019s handle. If you\u2019ve already created the Data Pump job, you can retrieve its handle by querying the DBA_DATAPUMP_JOBS view.<\/p>\n\n\n\n

SQL> desc DBA_DATAPUMP_JOBS\n Name                                      Null?    Type\n ----------------------------------------- -------- ----------------------------\n OWNER_NAME                                         VARCHAR2(128)\n JOB_NAME                                           VARCHAR2(128)\n OPERATION                                          VARCHAR2(128)\n JOB_MODE                                           VARCHAR2(128)\n STATE                                              VARCHAR2(128)\n DEGREE                                             NUMBER\n ATTACHED_SESSIONS                                  NUMBER\n DATAPUMP_SESSIONS                                  NUMBER<\/code><\/pre>\n\n\n\n
SELECT job_name, job_mode, operation, state, job_id \nFROM DBA_DATAPUMP_JOBS \nWHERE job_name = '<job_name>';<\/code><\/pre>\n\n\n\n
Replace <job_name><\/strong><\/code> with the name of your Data Pump job.<\/p>\n\n\n\n
Stop the Job Using DBMS_DATAPUMP:<\/h3>\n\n\n\n
BEGIN\n   DBMS_DATAPUMP.detach(handle => '<job_handle>');\nEND;\n\/\n\n-- Or to stop a job completely:\nBEGIN\n   DBMS_DATAPUMP.stop_job(job_handle => '<job_handle>');\nEND;\n\/<\/code><\/pre>\n\n\n\n
This will stop the job immediately. You can also use stop_job<\/strong> to stop the job without detaching it, depending on your needs.<\/p>\n\n\n\n
2. Killing the Job Using Oracle Session Management<\/strong><\/h2>\n\n\n\n
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.<\/p>\n\n\n\n
Find the Session ID<\/strong>:<\/h3>\n\n\n\n
You need to find the session associated with the Data Pump job. You can do this by querying the v$session view:<\/p>\n\n\n\n
SELECT s.sid, s.serial#, s.username, s.program \nFROM v$session s \nWHERE s.username = 'SYS' \nAND s.program LIKE 'expdp%' OR s.program LIKE 'impdp%';<\/code><\/pre>\n\n\n\n
This query helps you identify sessions related to expdp or impdp.<\/p>\n\n\n\n
Kill the Session<\/h3>\n\n\n\n
ALTER SYSTEM KILL SESSION '<sid>,<serial#>' immediate;<\/code><\/pre>\n\n\n\n
Replace and with the actual values from the previous query. This will immediately terminate the session and stop the Data Pump job.<\/p>\n\n\n\n
3. Killing the Job Using ps and kill (Linux\/Unix)<\/h2>\n\n\n\n
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.<\/p>\n\n\n\n
Find the Process ID (PID):<\/h3>\n\n\n\n
You can use the ps command to find the process associated with expdp or impdp:<\/p>\n\n\n\n
ps -ef | grep expdp\nps -ef | grep impdp<\/code><\/pre>\n\n\n\n
Kill the Process:<\/h3>\n\n\n\n
kill -9 <pid><\/code><\/pre>\n\n\n\n
Replace with the actual process ID you obtained. The kill -9 command will forcefully terminate the process.<\/p>\n\n\n\n
4. Killing the Job Using Oracle Enterprise Manager (OEM)<\/strong><\/h2>\n\n\n\n
If you are using Oracle Enterprise Manager (OEM) to manage your Oracle database, you can kill export\/import jobs directly from the OEM interface.<\/p>\n\n\n\n
Steps:<\/h4>\n\n\n\n
    \n
  1. Log in to Oracle Enterprise Manager.<\/li>\n\n\n\n
  2. Navigate to the “Jobs”<\/strong> section.<\/li>\n\n\n\n
  3. Locate the running export\/import job.<\/li>\n\n\n\n
  4. Choose the “Kill”<\/strong> option from the job\u2019s context menu.<\/li>\n<\/ol>\n\n\n\n
    This is the easiest method for DBAs who prefer a graphical interface over command-line operations.<\/p>\n\n\n\n
    5. Killing the Job by Detaching with expdp or impdp Command Line<\/h2>\n\n\n\n
    Attach to a running job:<\/p>\n\n\n\n
    expdp <username>\/<password> attach=<job_name>\nimpdp <username>\/<password> attach=<job_name><\/code><\/pre>\n\n\n\n
    Example:<\/strong><\/p>\n\n\n\n
    expdp '\"\/ as sysdba\"' attach=SYS_EXPORT_SCHEMA01\nimpdp '\"\/ as sysdba\"' attach=SYS_IMPORT_SCHEMA01<\/code><\/pre>\n\n\n\n
    Then kill it.<\/p>\n\n\n\n
    Export > status  -- Displays job progress and status\nExport > kill    -- Terminates the running job immediately\nExport > detach  -- Detaches from the job, but it continues running\nExport > attach  -- Reconnects to a running job to manage it<\/code><\/pre>\n\n\n\n
    These options give you control over managing and monitoring Data Pump jobs while they are running.<\/p>\n\n\n\n
    Read More:<\/strong><\/p>\n\n\n\n