SQL Script

Database Scheduler

Updated: 20-Mar-2005
Version: 1.1

Description

Details Job Windows and Jobs in the 10g Scheduler.

Parameters

None

SQL Source

REM Copyright (C) Think Forward.com 1998- 2005. All rights reserved. 


spool jobs.lst

PROMPT
PROMPT the job_queue_prcesses must be set in order to use the job_scheduler
PROMPT

show parameter job

col window_name form a18
col schedule_name form a20
col resource_plan form a10

PROMPT
PROMPT Display the job window configuration
PROMPT

select window_group_name, number_of_windows from dba_scheduler_window_groups;

select * from dba_scheduler_wingroup_members;

col repeat_interval form a45 wrap

select window_name,resource_plan,schedule_name,next_start_date 
from dba_scheduler_windows;

PROMPT
PROMPT Display information about scheduler

col repeat_interval form a50 wrap

select schedule_name,repeat_interval from dba_scheduleR_schedules;

PROMPT
PROMPT Display the scheduled jobs

col job_name form a18
col schedule_name form a20
col program_name form a20

select job_name, schedule_name, program_name, state
from dba_scheduler_jobs;

PROMPT
PROMPT Display the defined programs

col number_of_arguments form 99999 heading "No Arg"
col program_action form a42
col comments form a50 wrap

select program_name, program_type, program_action
from dba_scheduler_programs;

select program_name, number_of_arguments, comments
from dba_scheduler_programs;

PROMPT
PROMPT Display Program Arguments

col value form a20 wrap

select job_name,argument_name,value from  dba_scheduler_job_args;

PROMPT
PROMPT Display any active jobs

col job_name form a20
col resource_consumer_group form a18
col start_Date form a18 heading "Start Date"
col mins form 9,999 heading "Mins"
col secs form 99 Heading "Secs"
col cpu_secs form 9,999.99 Heading "CPU Secs"

select job_name, resource_consumer_group
      , to_char(sysdate-elapsed_time,'dd-mon-yyyy hh24:mi') start_date
      , extract(minute from elapsed_time) mins
      , extract(second from elapsed_time) secs
      , cpu_used/100 cpu_secs
from dba_scheduler_running_jobs;

PROMPT 
PROMPT Display history of job instances

col status form a9
col run_duration form a15

select job_name, status, error#,run_duration, cpu_used 
from dba_scheduler_job_run_details
where job_name not in ('PURGE_LOG');

PROMPT 
PROMPT Display job log details

col log_date form a35
col operation form a10

select log_date, job_name,status, operation 
from dba_scheduleR_job_log
where job_name not in ('PURGE_LOG')
order by log_date;


spool off

Return to Index of SQL Scripts

Site Map

SQL Script

Database Scheduler

Updated: 20-Mar-2005Version: 1.1

Description

Parameters

SQL Source

Site Map

Updated: 20-Mar-2005
Version: 1.1