Oracle Database JOB Scheduler

Oracle Jobs: DBA_JOBS, DBA_SCHEDULER_JOBS and Auto Tasks

Summary

Oracle Database provides different mechanisms for scheduling and managing jobs: DBA_JOBS (deprecated since 10g), DBA_SCHEDULER_JOBS (the current mechanism), and DBA_AUTO_TASKS (internal Oracle jobs). This article documents how to query, monitor, enable/disable, create, and manage these jobs.

1) Types of Jobs

• DBA_JOBS – Deprecated since 10g, requires explicit COMMIT.
• DBA_SCHEDULER_JOBS – Current job scheduling mechanism.
• DBA_AUTO_TASKS – Oracle internal jobs for maintenance and tuning.

2) DBA_JOBS

2.1 Query all DBA_JOBS

SET LINESIZE 1000 PAGESIZE 1000

COLUMN log_user FORMAT A15
COLUMN priv_user FORMAT A15
COLUMN schema_user FORMAT A15
COLUMN interval FORMAT A40
COLUMN what FORMAT A50
COLUMN nls_env FORMAT A50
COLUMN misc_env FORMAT A50

SELECT a.job,
       a.log_user,
       a.priv_user,
       a.schema_user,
       To_Char(a.last_date,'DD-MON-YYYY HH24:MI:SS') AS last_date,
       To_Char(a.next_date,'DD-MON-YYYY HH24:MI:SS') AS next_date,
       a.broken,
       a.interval,
       a.failures,
       a.what,
       a.total_time,
       a.nls_env,
       a.misc_env
FROM   dba_jobs a;

SET LINESIZE 80 PAGESIZE 14;

2.2 Query running DBA_JOBS

SET LINESIZE 500
SET PAGESIZE 1000
SET VERIFY OFF

SELECT a.job "Job",
       a.sid,
       a.failures "Failures",
       Substr(To_Char(a.last_date,'DD-Mon-YYYY HH24:MI:SS'),1,20) "Last Date",
       Substr(To_Char(a.this_date,'DD-Mon-YYYY HH24:MI:SS'),1,20) "This Date"
FROM   dba_jobs_running a;

SET PAGESIZE 14
SET VERIFY ON;

2.3 Manage DBA_JOBS

Disable:

EXEC DBMS_JOB.BROKEN(<JOB_ID>, TRUE);
COMMIT;

Enable:

EXEC DBMS_JOB.BROKEN(<JOB_ID>, FALSE);
COMMIT;

Remove:

EXEC DBMS_JOB.REMOVE(<JOB_ID>);
COMMIT;

2.4 Create DBA_JOBS Examples

Start at 06:00 and run every hour:

dbms_job.submit(
  :jobno, 'BEGIN statspack_alert_proc; END;',
  trunc(sysdate)+6/24,
  'trunc(SYSDATE+1/24,''HH'')',
  TRUE,
  :instno);
/
commit;

Start at 09:00 and repeat every 12 hours:

dbms_job.submit(
  :jobno,
  'BEGIN statspack_alert_proc; END;',
  trunc(sysdate+1)+9/24,
  'trunc(SYSDATE+12/24,''HH'')',
  TRUE,
  :instno);
/
commit;

Start at 06:00 and run every 10 minutes:

dbms_job.submit(
  :jobno,
  'BEGIN statspack_alert_proc; END;',
  trunc(sysdate+1)+6/24,
  'trunc(sysdate+1/144,''MI'')',
  TRUE,
  :instno);
/
commit;

Start at 06:00 every hour, Monday–Friday:

dbms_job.submit(
  :jobno,
  'BEGIN statspack_alert_proc; END;',
  trunc(sysdate+1)+6/24,
  trunc(
    least(
      next_day(SYSDATE - 1,'MONDAY'),
      next_day(SYSDATE - 1,'TUESDAY'),
      next_day(SYSDATE - 1,'WEDNESDAY'),
      next_day(SYSDATE - 1,'THURSDAY'),
      next_day(SYSDATE - 1,'FRIDAY')
    )+1/24,'HH'),
  TRUE,
  :instno);
/
commit;

3) DBA_SCHEDULER_JOBS

3.1 Query DBA_SCHEDULER_JOBS

SET LINESIZE 200

COLUMN owner FORMAT A20
COLUMN job_name FORMAT A30
COLUMN job_class FORMAT A30
COLUMN next_run_date FORMAT A36

SELECT owner,
       job_name,
       enabled,
       job_class,
       next_run_date
FROM   dba_scheduler_jobs
ORDER BY owner, job_name;

3.2 Running DBA_SCHEDULER_JOBS

SET LINESIZE 200

COLUMN owner FORMAT A20
COLUMN elapsed_time FORMAT A30

SELECT owner,
       job_name,
       running_instance,
       elapsed_time,
       session_id
FROM   dba_scheduler_running_jobs
ORDER BY owner, job_name;

3.3 History

SELECT * FROM DBA_SCHEDULER_JOB_LOG;

SELECT * FROM DBA_SCHEDULER_JOB_RUN_DETAILS;

3.4 Create Scheduler Jobs

Basic PLSQL Block:

BEGIN
  DBMS_SCHEDULER.create_job (
    job_name        => 'test_full_job_definition',
    job_type        => 'PLSQL_BLOCK',
    job_action      => 'BEGIN my_job_procedure; END;',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'freq=hourly; byminute=0; bysecond=0;',
    enabled         => TRUE);
END;
/

Stored procedure:

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name           =>  'update_sales',
    job_type           =>  'STORED_PROCEDURE',
    job_action         =>  'OPS.SALES_PKG.UPDATE_SALES_SUMMARY',
    start_date         =>  '28-APR-08 07.00.00 PM Australia/Sydney',
    repeat_interval    =>  'FREQ=DAILY;INTERVAL=2', -- every other day
    end_date           =>  '20-NOV-08 07.00.00 PM Australia/Sydney',
    auto_drop          =>   FALSE,
    job_class          =>  'batch_update_jobs',
    comments           =>  'My new job');
END;
/

Using a saved program:

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name          =>  'my_new_job1',
    program_name      =>  'my_saved_program',
    repeat_interval   =>  'FREQ=DAILY;BYHOUR=12',
    comments          =>  'Daily at noon');
END;
/

3.5 Manage Scheduler Jobs

Run job:

BEGIN
  dbms_scheduler.run_job (job_name => '<JOB1>');
END;
/

Stop job:

BEGIN
  DBMS_SCHEDULER.STOP_JOB('<JOB1>');
END;
/

Disable jobs:

BEGIN
  DBMS_SCHEDULER.DISABLE('<JOB1>, <JOB2>, <JOB3>');
END;
/

Enable jobs:

BEGIN
  DBMS_SCHEDULER.ENABLE('<JOB1>, <JOB2>, <JOB3>');
END;
/

Drop job:

BEGIN
  DBMS_SCHEDULER.drop_schedule (schedule_name => '<JOB1>');
END;
/

4) DBA_AUTO_TASKS

4.1 List Auto Tasks

SET LINES 180 PAGES 1000
COL CLIENT_NAME FOR A40
COL ATTRIBUTES FOR A60

SELECT CLIENT_NAME, STATUS, ATTRIBUTES, SERVICE_NAME
FROM   DBA_AUTOTASK_CLIENT;

4.2 Check Execution History

SET PAGES 200 LIN 250
COL "JOB NAME" FOR A40
COL "WIN NAME" FOR A20
COL WINDOW_START_TIME FOR A40
COL WINDOW_END_TIME FOR A40
COL WINDOW_DURATION FOR A40

SELECT CLIENT_NAME "JOB NAME",
       WINDOW_NAME "WIN NAME",
       WINDOW_START_TIME,
       WINDOW_END_TIME,
       WINDOW_DURATION
FROM   DBA_AUTOTASK_CLIENT_HISTORY
ORDER BY CLIENT_NAME, WINDOW_START_TIME;

4.3 Disable Default Maintenance Windows (having them enabled requires EE)

Disable Auto Space Advisor:

BEGIN
  DBMS_AUTO_TASK_ADMIN.disable(
    client_name => 'auto space advisor',
    operation   => NULL,
    window_name => NULL);
END;
/

Disable Auto SQL Tuning Advisor:

BEGIN
  DBMS_AUTO_TASK_ADMIN.disable(
    client_name => 'sql tuning advisor',
    operation   => NULL,
    window_name => NULL);
END;
/

Disable Auto Optimizer Stats Collection:

BEGIN
  DBMS_AUTO_TASK_ADMIN.disable(
    client_name => 'auto optimizer stats collection',
    operation   => NULL,
    window_name => NULL);
END;
/