Oracle Database - Scheduler Job

> Database > Oracle Database

1 - About

The scheduler of the Oracle Database.

3 - Management

3.1 - Set

For example, to change the Monday maintenance window so that it starts at 5 a.m., execute the following PL/SQL program:

BEGIN 
  DBMS_SCHEDULER.SET_ATTRIBUTE (
    'MONDAY_WINDOW'
,   'repeat_interval'
,   'freq=daily;byday=MON;byhour=05;byminute=0;bysecond=0'
);
END;
/
Advertising

3.2 - List

3.2.1 - Job

Below is a set of important columns for jobs.

SELECT
  job_name,
  round(CAST(next_run_date AS DATE) - CAST (last_start_date AS DATE),2) AS diff,
  last_start_date,
  next_run_date,
  state,
  repeat_interval,
  restartable,
  all_SCHEDULER_JOBS.*
FROM
  ALL_SCHEDULER_JOBS

3.2.2 - Metadata / Views

SELECT DISTINCT
  '(DBA|USER|ALL)_' || SUBSTR(object_name,instr(object_name,'_')+1) AS schedulerView
FROM
  all_objects
WHERE
  object_name LIKE '%SCHED%'
AND object_type = 'VIEW'
AND
  (
    object_name LIKE 'USER_%'
  OR object_name LIKE 'DBA_%'
  OR object_name LIKE 'ALL_%'
  )
ORDER BY
  schedulerView ;
Scheduler Views
(DBA|USER|ALL)_AUTOTASK_SCHEDULE
(DBA|USER|ALL)_IV_CALENDAR_SCHEDULES
(DBA|USER|ALL)_IV_SCHEDULABLE
(DBA|USER|ALL)_QUEUE_SCHEDULES
(DBA|USER|ALL)_SCHEDULER_CHAIN_RULES
(DBA|USER|ALL)_SCHEDULER_CHAINS
(DBA|USER|ALL)_SCHEDULER_CHAIN_STEPS
(DBA|USER|ALL)_SCHEDULER_CREDENTIALS
(DBA|USER|ALL)_SCHEDULER_DB_DESTS
(DBA|USER|ALL)_SCHEDULER_DESTS
(DBA|USER|ALL)_SCHEDULER_EXTERNAL_DESTS
(DBA|USER|ALL)_SCHEDULER_FILE_WATCHERS
(DBA|USER|ALL)_SCHEDULER_GLOBAL_ATTRIBUTE
(DBA|USER|ALL)_SCHEDULER_GROUP_MEMBERS
(DBA|USER|ALL)_SCHEDULER_GROUPS
(DBA|USER|ALL)_SCHEDULER_JOB_ARGS
(DBA|USER|ALL)_SCHEDULER_JOB_CLASSES
(DBA|USER|ALL)_SCHEDULER_JOB_DESTS
(DBA|USER|ALL)_SCHEDULER_JOB_LOG
(DBA|USER|ALL)_SCHEDULER_JOB_ROLES
(DBA|USER|ALL)_SCHEDULER_JOB_RUN_DETAILS
(DBA|USER|ALL)_SCHEDULER_JOBS
(DBA|USER|ALL)_SCHEDULER_NOTIFICATIONS
(DBA|USER|ALL)_SCHEDULER_PROGRAM_ARGS
(DBA|USER|ALL)_SCHEDULER_PROGRAMS
(DBA|USER|ALL)_SCHEDULER_REMOTE_DATABASES
(DBA|USER|ALL)_SCHEDULER_REMOTE_JOBSTATE
(DBA|USER|ALL)_SCHEDULER_RUNNING_CHAINS
(DBA|USER|ALL)_SCHEDULER_RUNNING_JOBS
(DBA|USER|ALL)_SCHEDULER_SCHEDULES
(DBA|USER|ALL)_SCHEDULER_WINDOW_DETAILS
(DBA|USER|ALL)_SCHEDULER_WINDOW_GROUPS
(DBA|USER|ALL)_SCHEDULER_WINDOW_LOG
(DBA|USER|ALL)_SCHEDULER_WINDOWS
(DBA|USER|ALL)_SCHEDULER_WINGROUP_MEMBERS
(DBA|USER|ALL)_WORKLOAD_REPLAY_SCHEDULES
(DBA|USER|ALL)_WORKLOAD_SCHEDULE_CAPTURES
(DBA|USER|ALL)_WORKLOAD_SCHEDULE_ORDERING
Advertising

4 - Documentation / Reference

db/oracle/scheduler.txt ยท Last modified: 2017/09/06 19:29 by gerardnico