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
Leave a Reply