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;
  /
  
•