In my never ending frustrations with using Oracle (seriously, I loathe Oracle above all else), I could not find an absolute answer on how to stop or kill or delete data pump jobs being executed. I found the answer via Metalink, and I’m going to share it because I feel these answers should be easily accessible. It’s a two step process.

1. Get the list of datapump jobs:

SET lines 200
COL owner_name FORMAT a10;
COL job_name FORMAT a20
COL state FORMAT a11
COL operation LIKE state
COL job_mode LIKE state

-- locate Data Pump jobs:

SELECT owner_name, job_name, operation, job_mode,
state, attached_sessions
FROM dba_datapump_jobs
WHERE job_name NOT LIKE 'BIN$%'
ORDER BY 1,2;

The output might look something like this:

OWNER_NAME JOB_NAME             OPERATION   JOB_MODE    STATE       ATTACHED_SESSIONS
---------- -------------------- ----------- ----------- ----------- -----------------
SCHEMA_USER SYS_IMPORT_SCHEMA_01 IMPORT      SCHEMA      EXECUTING                   1

There are two things needed to perform the kill:
1. OWNER_NAME (Which is SCHEMA_USER)
2. JOB_NAME (Which is SYS_IMPORT_SCHEMA_01)

With that information, we can now stop and kill the job:

SET serveroutput on
SET lines 100
DECLARE
   h1 NUMBER;
BEGIN
  -- Format: DBMS_DATAPUMP.ATTACH('[job_name]','[owner_name]');
   h1 := DBMS_DATAPUMP.ATTACH('SYS_IMPORT_SCHEMA_01','SCHEMA_USER');
   DBMS_DATAPUMP.STOP_JOB (h1,1,0);
END;
/

Check that the job has stopped:

SQL> SET lines 200
SQL> COL owner_name FORMAT a10;
SQL> COL job_name FORMAT a20
SQL> COL state FORMAT a11
SQL> COL operation LIKE state
SQL> COL job_mode LIKE state
SQL>
SQL> -- locate Data Pump jobs:
SQL>
SQL> SELECT owner_name, job_name, operation, job_mode,
  2  state, attached_sessions
  3  FROM dba_datapump_jobs
  4  WHERE job_name NOT LIKE 'BIN$%'
  5  ORDER BY 1,2;

no rows selected