Script:List Grid Control Jobs

以下脚本可以用于列出Grid Control中的定式作业:

SET verify OFF
SET linesize 255
SET pagesize 128
SET trimout ON
SET trimspool ON
SPOOL jobdump.log
ALTER SESSION SET nls_date_format='MON-DD-YYYY hh:mi:ss pm';
COLUMN status format a15
COLUMN job_name FORMAT a64
COLUMN job_type FORMAT a32
COLUMN job_owner FORMAT a32
COLUMN job_status format 99
COLUMN target_type format a64
COLUMN frequency_code format a20
COLUMN  interval format 99999999
VARIABLE JOBID VARCHAR2(64);
PROMPT *********************** JOB INFO ********************************
REM Get the job id
SET serveroutput on
BEGIN
SELECT job_id INTO :JOBID
FROM   MGMT_JOB
WHERE  job_name='&&jobName'
AND    job_owner='&&jobOwner'
AND    nested=0;
EXCEPTION
WHEN NO_DATA_FOUND THEN
BEGIN
DBMS_OUTPUT.put_line('JOB NOT FOUND, TRYING NAME ONLY');
SELECT job_id INTO :JOBID
FROM   MGMT_JOB
WHERE  job_name='&&jobName'
AND    nested=0
AND    ROWNUM=1;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.put_line('JOB NOT FOUND');
END;
END;
/
SELECT  job_name, job_owner, job_type, system_job, job_status, target_type
FROM    MGMT_JOB
WHERE   job_id=HEXTORAW(:JOBID);
PROMPT *********************** JOB SCHEDULE ****************************
SELECT  DECODE(frequency_code,
1, 'Once',
2, 'Interval',
3, 'Daily',
4, 'Day of Week',
5, 'Day of Month',
6, 'Day of Year', frequency_code) "FREQUENCY_CODE",
start_time, end_time, execution_hours, execution_minutes,
interval, months, days, timezone_info, timezone_target_index,
timezone_offset, timezone_region
FROM    MGMT_JOB_SCHEDULE s, MGMT_JOB j
WHERE   s.schedule_id=j.schedule_id
AND     j.job_id=HEXTORAW(:JOBID);
PROMPT ********************** PARAMETERS ********************************
SELECT  parameter_name,
decode(parameter_type,
0, 'Scalar',
1, 'Vector',
2, 'Large', parameter_type) "PARAMETER_TYPE",
scalar_value, vector_value
FROM    MGMT_JOB_PARAMETER
WHERE   job_id=HEXTORAW(:JOBID)
AND     execution_id=HEXTORAW('0000000000000000')
ORDER BY parameter_name;
PROMPT ********************** TARGETS ********************************
SELECT  target_name, target_type
FROM    MGMT_JOB_TARGET jt, MGMT_TARGETS t
WHERE   job_id=HEXTORAW(:JOBID)
AND     execution_id=HEXTORAW('0000000000000000')
AND     jt.target_guid=t.target_guid
ORDER BY target_type, target_name;
PROMPT ********************** FLAT TARGETS ********************************
SELECT  target_name, target_type
FROM    MGMT_JOB_FLAT_TARGETS jft, MGMT_TARGETS t
WHERE   job_id=HEXTORAW(:JOBID)
AND     jft.target_guid=t.target_guid
ORDER BY target_type, target_name;
PROMPT ************************ EXECUTIONS *******************************
SELECT  execution_id,
DECODE(status,
1, 'SCHEDULED',
2, 'RUNNING',
3, 'FAILED INIT',
4, 'FAILED',
5, 'SUCCEEDED',
6, 'SUSPENDED',
7, 'AGENT DOWN',
8, 'STOPPED',
9, 'SUSPENDED/LOCK',
10, 'SUSPENDED/EVENT',
11, 'SUSPENDED/BLACKOUT',
12, 'STOP PENDING',
13, 'SUSPEND PENDING',
14, 'INACTIVE',
15, 'QUEUED',
16, 'FAILED/RETRIED',
17, 'WAITING',
18, 'SKIPPED', status) "STATUS",
scheduled_time, start_time, end_time
FROM    MGMT_JOB_EXEC_SUMMARY e
WHERE   job_id=HEXTORAW(:JOBID)
ORDER BY scheduled_time;
UNDEFINE jobName
UNDEFINE jobOwner
UNDEFINE JOBID
SPOOL OFF

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

沪ICP备14014813号

沪公网安备 31010802001379号