Check Job Status

@/u01/dba/oraschedjobstatus.sql

@/u01/dba/oraschedjobstatusbyjobname.sql

@/u01/dba/oraschedrunningjobstatusbyjobname.sql 

Check Job History

@/u01/dba/oraschedjobhistory.sql

Check Job Definition

You can use DBMS_METADATA to extract the job definition DDL...

@/u01/dba/oraschedjobdef.sql

Enabling/Disabling Jobs

To prevent a job running (without removing it's definition) you should disable it. To reinstate the job schedule you should then enable it again.

Enable a job

EXEC DBMS_SCHEDULER.ENABLE(JOB_NAME => '"&OWNER"."&JOB"');

Stop a running job

EXEC DBMS_SCHEDULER.STOP_JOB('"&OWNER"."&JOB"');

EXEC DBMS_SCHEDULER.STOP_JOB(JOB_NAME => '"&OWNER"."&JOB"', FORCE => TRUE);

Disable a job

EXEC DBMS_SCHEDULER.DISABLE('"&OWNER"."&JOB"');

EXEC DBMS_SCHEDULER.DISABLE(JOB_NAME => '"&OWNER"."&JOB"', FORCE => TRUE);

Disable All Jobs

@/u01/dba/orascheddisablealljobs.sql 

@/tmp/tmporascheddisablealljobs.sql

Disable ALL Jobs for a specified Owner

@/u01/dba/orascheddisablealljobsbyowners.sql 

@/tmp/tmporascheddisablealljobsbyowners.sql 

Troubleshooting

ORA-27366: job "OWNER"."JOB" is not running 

This can happen when the Scheduler’s metadata says “RUNNING”, but the actual execution process is gone (e.g., the slave process crashed or was killed).

EXEC DBMS_SCHEDULER.DISABLE(JOB_NAME => '"&OWNER"."&JOB"', FORCE => TRUE);

EXEC DBMS_SCHEDULER.PURGE_LOG(JOB_NAME => '"&OWNER"."&JOB"');

EXEC DBMS_SCHEDULER.ENABLE(JOB_NAME => '"&OWNER"."&JOB"');

If the job is still showing incorrectly, a last resort is to drop and recreate the job...

SET LONG 2000

SELECT DBMS_METADATA.GET_DDL('PROCOBJ', '&JOB', '&OWNER') FROM dual;

Tidy up the output to avoid formatting errors (like line breaks) that would prevent the code from running.

Drop the job...

EXEC DBMS_SCHEDULER.DROP_JOB('"&OWNER"."&JOB"', force => TRUE);

Recreate the job...

ALTER SESSION SET CURRENT_SCHEMA=&OWNER;

BEGIN

  dbms_scheduler.create_job...

Bibliography