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
