Stopping / Killing oracle datapump job from SQL

There are two ways to stop the datapump job in oracle.

One way I have described earlier here in which we attach to the expdp session and kill_job from expdp command prompt.

However there is another way of doing the same thing and that is from SQL prompt.

In this post I will give step by step instructions on how to stop the job. I am using oracle 12c

I have got my export running and when looked under dba_datapump_jobs I can see it executing 

SQL> col owner_name for a20
SQL> col job_name for a30
SQL> col OPERATION for a20
SQL> SELECT owner_name, job_name, rtrim(operation) "OPERATION", rtrim(job_mode) "JOB_MODE", state, attached_sessions, DATAPUMP_SESSIONS, DEGREE
  2  FROM dba_datapump_jobs WHERE job_name NOT LIKE 'BIN$%' ORDER BY 1,2;

OWNER_NAME           JOB_NAME                       OPERATION            JOB_MODE                       STATE                          ATTACHED_SESSIONS DATAPUMP_SESSIONS     DEGREE
-------------------- ------------------------------ -------------------- ------------------------------ ------------------------------ ----------------- ----------------- ----------
SYSTEM               SYS_EXPORT_FULL_01             EXPORT               FULL                           EXECUTING                                      1                 6          4

SQL>

This output have all the information to stop that job. Information needed is owner_name and job_name

Execute the command below as sysdba or system from SQL>

SQL> SET serveroutput on
SQL> set lines 270
SQL> DECLARE
  2   job1 NUMBER;
  3  BEGIN
  4   job1 := DBMS_DATAPUMP.ATTACH('SYS_EXPORT_FULL_01','SYSTEM');
  5   DBMS_DATAPUMP.STOP_JOB (job1,1,0,20);
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL>

Please note that it may take few minutes to return the prompt back, but you will immediately see the log entry in the export log that it is stopped

Job "SYSTEM"."SYS_EXPORT_FULL_01" stopped due to fatal error at Tue Sep 4 10:04:39 2018 elapsed 0 00:04:36

This execution of procedure will drop the table SYSTEM.SYS_EXPORT_FULL_01.

For documentation on STOP_JOB please refer back to the oracle documentation.