Gather all information related to dba scheduler using a single script

 In our daily life, sometimes we require to gather various information related to different jobs or jobs scheduled in dba_scheduler  and for many of us it becomes challenging to remember all the views to gather that information.

 

Using this script, we can gather various information related to undo tablespace in a single go.

 [db@server102 ~]$ cat scheduler.sql

Rem Please execute following script to generate html file

 

set markup html on spool on

SPOOL Scheduler_Views.HTML

set pagesize 200

set echo on

select systimestamp from dual;

select sessiontimezone from dual;

select dbms_scheduler.stime from dual;

select dbms_scheduler.get_sys_time_zone_name from dual;

select instance_name,host_name,version,to_char(startup_time, 'DD-MON-YY HH:MI:SS AM') startup_time from v$instance;

select * from dba_scheduler_global_attribute;

show parameter job_queue

/

 

-- ** Autotask **

select client_name, status from dba_autotask_client;

select * from dba_autotask_client;

select * from dba_autotask_client_history order by window_start_time desc;

 

-- ** Windows and Window Group **

select * from dba_scheduler_window_groups;

select * from dba_scheduler_wingroup_members order by 1,2;

select window_name,enabled,active,resource_plan,comments from dba_scheduler_windows;

select window_name,enabled,active,resource_plan,repeat_interval,duration,last_start_date,next_start_date from dba_scheduler_windows;

select * from dba_scheduler_windows;

 

-- ** Window history **

select log_date, window_name, operation,status, substr(additional_info,1,350) Info

from dba_scheduler_window_log order by 1 desc

/

select log_date,window_name,req_start_date,actual_start_date,window_duration,actual_duration,additional_info

from dba_scheduler_window_details

where rownum<20 order by log_date desc

/

 

-- ** DBA JOBS **

select owner,job_name,repeat_interval,enabled,state,run_count,last_start_date,next_run_date

from dba_scheduler_jobs

order by owner,enabled

/

select owner,job_name,enabled,state,job_style,job_creator,program_owner,program_name,job_type,

schedule_owner,schedule_name,schedule_type,start_date,repeat_interval,

job_class,job_priority,run_count,max_runs,failure_count,max_failures,retry_count,last_start_date,

last_run_duration,next_run_date

from dba_scheduler_jobs

order by owner,enabled

/

select * from dba_scheduler_jobs;

 

-- ** Running Jobs **

select * from dba_scheduler_running_jobs;

 

-- ** Schedule **

select * from dba_scheduler_schedules;

 

-- ** JOB History **

select to_char(log_date, 'DD-MON-YY HH24:MI:SS') timestamp, owner,job_name, status,error#,req_start_date,actual_start_date,additional_info

from dba_scheduler_job_run_details

order by log_date desc ;

 

-- ** Resource Manager **

show parameter resource_manager_plan

select plan,status from dba_rsrc_plans;

select * from dba_rsrc_plan_directives ;

select * from dba_rsrc_plans ;

spool off

set markup html off spool off

exit


Execute the scheduler.sql

[db@server102 ~]$ sqlplus / as sysdba 

SQL> @scheduler.sql


1 comment: