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

沪ICP备14014813号

沪公网安备 31010802001379号