Script:诊断Scheduler信息

以下脚本可以用于针对10g以后的Scheduler信息:

set long 400
set pagesize 1000
set linesize 120
column log_date format a37
column operation format a8
column status format a6
column additional_info format a400
column systimestamp format a37
column next_start_date format a40
column start_date format a40
column manual_open_time format a40
column manual_duration format a40
column duration format a40
column end_date format a40
column last_start_date format a40
column window_name format a26
column systimestamp format a35
column ATTR_INTV format a37
column ATTR_TSTAMP format a37
column start_time format a35
column obj_name format a20
column name format a30
column value format a30
column job_queue_name format a18  
column job_type format 9999
column flag format 9999  
column status format a6
column "SEQUENCE#" format 9999
column id format 99999
spool wintest
select * from dba_scheduler_windows ;
select log_date, window_name, operation,status, substr(additional_info,1,350)
 from dba_scheduler_window_log order by 1 ;
select window_name, substr(additional_info,1,350) x 
 from dba_scheduler_window_log where additional_info is not null;

-- Report current time in scheduler format
select dbms_scheduler.stime from dual;
-- Report Internal Queues v10.2(before)
select job_queue_name,   JOB_TYPE,  
    (select object_name from dba_objects where object_id = job_oid
      ) OBJ_NAME,  FLAG, start_time from x$jskjobq;
-- Report current open window attributes(before)
SELECT o.name, o.namespace, a.*
　FROM sys.obj$ o, sys.scheduler$_global_attribute a
　　WHERE o.obj# = a.obj# AND BITAND(a.flags,1) != 1
　　　AND o.name = 'CURRENT_OPEN_WINDOW' AND o.namespace = 51;

-- To implement the solution, please execute the following steps::
exec dbms_scheduler.disable('WEEKEND_WINDOW');
exec dbms_scheduler.disable('WEEKNIGHT_WINDOW');
exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED', 'TRUE');
select value from v$parameter where name='job_queue_processes';
alter system set job_queue_processes=0;
exec dbms_ijob.set_enabled(FALSE);
alter system flush shared_pool;
alter system flush shared_pool;

declare
cwo number;
begin
select o.obj# into cwo from sys.obj$ o
  where o.name = 'CURRENT_OPEN_WINDOW' and o.namespace = 51;
update sys.scheduler$_global_attribute set value = null, attr_tstamp = null,
attr_intv = null, additional_info = null where obj# = cwo;
end;
/
exec dbms_ijob.set_enabled(TRUE);
exec dbms_scheduler.enable('WEEKEND_WINDOW');
exec dbms_scheduler.enable('WEEKNIGHT_WINDOW');
exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED', 'FALSE');
exec dbms_ijob.set_enabled(TRUE);

-- Report current time in scheduler format
select dbms_scheduler.stime from dual;
-- Report Internal Queues v10.2(after)
select job_queue_name, JOB_TYPE,
 (select object_name from dba_objects where object_id = job_oid
  ) OBJ_NAME, FLAG, start_time from x$jskjobq;
-- Report current open window attributes(after)
SELECT o.name, o.namespace, a.*
 FROM sys.obj$ o, sys.scheduler$_global_attribute a
  WHERE o.obj# = a.obj# AND BITAND(a.flags,1) != 1
   AND o.name = 'CURRENT_OPEN_WINDOW' AND o.namespace = 51;

ACCEPT J_NUM NUMBER PROMPT "Enter job_queue_processes: "
prompt Setting job_queue_processes=&&J_NUM
alter system set job_queue_processes=&&J_NUM;
select value from v$parameter where name='job_queue_processes';
spool off