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.

Running Tuning Adviser from sqlplus

-- Get the SQL ID
5s1uq1cdc2rzp

-- get the explain plan
select plan_table_output from table(dbms_xplan.display_cursor('5s1uq1cdc2rzp',null,'basic'));


-- Create tuning taks for the SQL ID
DECLARE
  v_tune_taskid  VARCHAR2(100);
BEGIN
  v_tune_taskid := dbms_sqltune.create_tuning_task (
                          sql_id      => '5s1uq1cdc2rzp',
                          scope       => dbms_sqltune.scope_comprehensive,
                          time_limit  => 30,
                          task_name   => 'sqltune_5s1uq1cdc2rzp',
                          description => 'Tuning task sql_id 5s1uq1cdc2rzp');
  dbms_output.put_line('taskid = ' || v_tune_taskid);
END;
/

-- Run the Tuning Task
exec dbms_sqltune.execute_tuning_task(task_name => 'sqltune_5s1uq1cdc2rzp');

-- Check the status for the task
select task_name, status
from dba_advisor_log
where owner = '&OWNER_YOU_RAN_TASK_AS';   -- sys in my case


-- Get the report
set long 10000;
set pagesize 1000
set linesize 220
set pagesize 24
select dbms_sqltune.report_tuning_task('sqltune_5s1uq1cdc2rzp') as output
from dual;