/*
|| Oracle 10g Scheduler - Listing 4
||
|| Contains useful queries for obtaining metadata about Oracle 10g Scheduler
|| objects that support advanced features.
||
|| Author: Jim Czuprynski
||
|| Usage Notes:
|| This script is provided to demonstrate various features of Oracle 10g
|| Scheduler and should be carefully proofread before executing it against
|| any existing Oracle database to insure that no potential damage can occur.
||
*/
 
-----
-- Listing 4.1: What Scheduler Job Classes are available?
-----
TTITLE 'Scheduler Job Classes:'
COL job_class_name              FORMAT A20      HEADING 'Job Class'
COL resource_consumer_group     FORMAT A32      HEADING 'Resource Consumer Group'
COL logging_level               FORMAT A08      HEADING 'Logging|Level'
COL log_history                 FORMAT 9999     HEADING 'Log|Hist|Days|Kept'

SELECT
     job_class_name
    ,resource_consumer_group
    ,logging_level
    ,log_history
  FROM dba_scheduler_job_classes
;
 
-----
-- Listing 4.2: What Scheduler Windows are available?
-----
TTITLE 'Scheduler Windows:'
COL window_name         FORMAT A20      HEADING 'Window Name'
COL resource_plan       FORMAT A20      HEADING 'Resource Plan'
COL start_date          FORMAT A32      HEADING 'Starts On'
COL enabled             FORMAT A08      HEADING 'Enabled?'
COL active              FORMAT A08      HEADING 'Active?'
COL window_priority     FORMAT A10      HEADING 'Priority'
COL repeat_interval     FORMAT A60      HEADING 'Execution Interval' WRAP

SELECT
     window_name
    ,resource_plan
    ,start_date
    ,enabled
    ,active
    ,window_priority
    ,repeat_interval
  FROM dba_scheduler_windows
;
 
-----
-- Listing 4.3: Show details of Scheduler Windows
-----
TTITLE 'Scheduler Window Details:'
COL log_timestamp       FORMAT A16      HEADING 'Log Date'
COL window_name         FORMAT A12      HEADING 'Window Name'
COL req_startdate       FORMAT A20      HEADING 'Reqd Start Date'
COL act_startdate       FORMAT A20      HEADING 'Actual Start Date'
COL window_duration     FORMAT A16      HEADING 'Window Duration'
COL actual_duration     FORMAT A16      HEADING 'Actual Duration'

SELECT
     TO_CHAR(log_date, 'mm/dd/yyyy hh24:mi:ss') log_timestamp
    ,window_name
    ,TO_CHAR(req_start_date, 'mm/dd/yyyy hh24:mi:ss') req_startdate
    ,TO_CHAR(actual_start_date, 'mm/dd/yyyy hh24:mi:ss') act_startdate
    ,window_duration
    ,actual_duration
  FROM dba_scheduler_window_details
;
 
-----
-- Listing 4.4: What Window Groups are available?
-----
TTITLE 'Scheduler Window Groups:'
COL window_group_name   FORMAT A32      HEADING 'Window Group Name'
COL enabled             FORMAT A08      HEADING 'Enabled?'
COL number_of_windows   FORMAT 9999     HEADING '# of|Wins'

SELECT
     window_group_name
    ,enabled
    ,number_of_windows
  FROM dba_scheduler_window_groups
;
 
-----
-- Listing 4.5: What Scheduler Windows belong to Window Groups?
-----
TTITLE 'Scheduler Window Group Members:'
COL window_group_name   FORMAT A32      HEADING 'Window Group Name'
COL window_name         FORMAT A32      HEADING 'Window Name'

SELECT
     window_group_name
    ,window_name
  FROM dba_scheduler_wingroup_members
;