Stop / Drop Scheduled / Running jobs in Oracle

advertisements

_____________________________________________________________________________________________________________________

You can use the DBMS_SCHEDULER package itself to stop or drop the job. There are two separate procedure(stop_job and drop_job) exists in the package.In some cases you will have to use the force option to stop the job.


Example
SQL> SELECT job_name, state FROM DBA_SCHEDULER_JOBS where job_name like '%AT_OS_OPT%';


JOB_NAME
-----------------------
STATE
---------------
ORA$AT_OS_OPT_SY_1220
RUNNING
SQL> exec DBMS_SCHEDULER.stop_JOB (job_name => 'SYS.ORA$AT_OS_OPT_SY_1220');
BEGIN DBMS_SCHEDULER.stop_JOB (job_name => 'SYS.ORA$AT_OS_OPT_SY_1220'); END;

*
ERROR at line 1:
ORA-27366: job "SYS"."ORA$AT_OS_OPT_SY_1220" is not running
ORA-06512: at "SYS.DBMS_ISCHED", line 227
ORA-06512: at "SYS.DBMS_SCHEDULER", line 674
ORA-06512: at line 1

SELECT job_name, state FROM DBA_SCHEDULER_JOBS where job_name like 'ORA$AT%';
JOB_NAME
----------------------
STATE
---------------
ORA$AT_OS_OPT_SY_1220
RUNNING

Use force=True to stop the job forcefully.

SQL> exec DBMS_SCHEDULER.stop_JOB (job_name => 'SYS.ORA$AT_OS_OPT_SY_1220',force=>true);

PL/SQL procedure successfully completed.
SQL> SELECT job_name, state FROM DBA_SCHEDULER_JOBS where job_name like '%AT_OS_OPT%';
no rows selected
To drop the job

SQL> exec DBMS_SCHEDULER.drop_JOB (job_name => 'SYS.ORA$AT_OS_OPT_SY_1220',force=>true);

_____________________________________________________________________________________________________________________

Website Stats