Highly useful PL/SQL and SQL commands (DBAs)

Here are some short, highly useful PL/SQL and SQL commands frequently used by Oracle DBAs to manage the DBMS_SCHEDULER engine.

Quick Monitoring & Health Checks

• Find currently running jobs:
• sql

SELECT owner, job_name, elapsed_time, session_id FROM dba_scheduler_running_jobs;

• Check the last 10 job failures:
• sql

SELECT log_date, owner, job_name, status, error# 
FROM (SELECT * FROM dba_scheduler_job_run_details WHERE status = 'FAILED' ORDER BY log_date DESC) 
WHERE ROWNUM <= 10;

Mass Actions & Administration

• Disable all scheduler jobs globally:
• sql:

EXEC DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('SCHEDULER_DISABLED', 'TRUE');

• Stop a runaway or frozen job instantly:
• sql:

EXEC DBMS_SCHEDULER.STOP_JOB('SCHEMA_NAME.JOB_NAME', force => TRUE);

• Run a job immediately (ignoring its schedule):
  
• sql:

EXEC DBMS_SCHEDULER.RUN_JOB('SCHEMA_NAME.JOB_NAME', use_current_session => FALSE);

• (Setting use_current_session to FALSE runs it asynchronously in the background).

Purging and Cleaning
Drop all disabled jobs in a specific schema:

• sql:

BEGIN
FOR r IN (SELECT job_name FROM dba_scheduler_jobs WHERE owner = 'MY_SCHEMA' AND enabled = 'FALSE') LOOP
DBMS_SCHEDULER.DROP_JOB('MY_SCHEMA.' || r.job_name);
END LOOP;
END; /
/