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