为了更方便的监控数据库的运行状态,把数据库JOB的运行状态收集到一个表中,为了实现上面的功能,我们定义两个表:check_job,job_detail
check_job表用于存储JOB的运行状态
job_detail表用于存储JOB的功能,责任人,所在厂区
这两个表的详细结构如下:
SQL> desc check_job
Name Null? Type
----------------------------------------- -------- ----------------------------
HOST VARCHAR2(20)
JOB NUMBER
LOG_USER VARCHAR2(30)
LAST_DATE DATE
NEXT_DATE DATE
TOTAL_TIME NUMBER
BROKEN VARCHAR2(1)
INTERVAL VARCHAR2(200)
FAILURES NUMBER
WHAT VARCHAR2(4000)
RECORD_DATE DATE
SQL> desc job_detail
Name Null? Type
----------------------------------------- -------- ----------------------------
HOST VARCHAR2(20)
JOB NUMBER
FACTORY VARCHAR2(50)
PURPOSE VARCHAR2(100)
WHO VARCHAR2(20)
通过在数据库中定义如下的JOB收集JOB信息
insert into oscheck.check_job@check_link
select '10.182.15.40' as host, job,log_user,last_date,next_date,total_time,broken,interval,failures, what,sysdate as record_date
from dba_jobs where schema_user not in
('SYSTEM',
'SYS',
'OUTLN',
'DIP',
'TSMSYS',
'DBSNMP',
'WMSYS ',
'EXFSYS',
'DMSYS ',
'CTXSYS',
'XDB',
'ANONYMOUS',
'ORDPLUGINS',
'SI_INFORMTN_SCHEMA',
'ORDSYS',
'MDSYS',
'OLAPSYS',
'MDDATA',
'SYSMAN',
'MGMT_VIEW',
'SCOTT');
数据据的查询,使用union合并两个表的内容,保证在check_job中出现的信息就会被查询出来SQL如下:
select host as 主机,
null as 厂区,
log_user as 用户,
job as job号,
null as 负任人,
null as JOB说明,
last_date as 上次执行时间,
next_date as 下次执行时间,
failures as 失败次数,
record_date as 点检时间
from check_job
where record_date > trunc(sysdate)
minus
select host,
null,
log_user,
check_job.job,
null,
null,
last_date,
next_date,
failures,
record_date
from check_job,job_detail
where check_job.host = job_detail.host
and check_job.job = job_detail.job
and record_date > trunc(sysdate)
union
select host,
factory,
log_user,
check_job.job,
who,
purpose,
last_date,
next_date,
failures,
record_date
from job_detail, check_job
where check_job.host = job_detail.host
and check_job.job = job_detail.job
and record_date > trunc(sysdate)
;
check_job表用于存储JOB的运行状态
job_detail表用于存储JOB的功能,责任人,所在厂区
这两个表的详细结构如下:
SQL> desc check_job
Name Null? Type
----------------------------------------- -------- ----------------------------
HOST VARCHAR2(20)
JOB NUMBER
LOG_USER VARCHAR2(30)
LAST_DATE DATE
NEXT_DATE DATE
TOTAL_TIME NUMBER
BROKEN VARCHAR2(1)
INTERVAL VARCHAR2(200)
FAILURES NUMBER
WHAT VARCHAR2(4000)
RECORD_DATE DATE
SQL> desc job_detail
Name Null? Type
----------------------------------------- -------- ----------------------------
HOST VARCHAR2(20)
JOB NUMBER
FACTORY VARCHAR2(50)
PURPOSE VARCHAR2(100)
WHO VARCHAR2(20)
通过在数据库中定义如下的JOB收集JOB信息
insert into oscheck.check_job@check_link
select '10.182.15.40' as host, job,log_user,last_date,next_date,total_time,broken,interval,failures, what,sysdate as record_date
from dba_jobs where schema_user not in
('SYSTEM',
'SYS',
'OUTLN',
'DIP',
'TSMSYS',
'DBSNMP',
'WMSYS ',
'EXFSYS',
'DMSYS ',
'CTXSYS',
'XDB',
'ANONYMOUS',
'ORDPLUGINS',
'SI_INFORMTN_SCHEMA',
'ORDSYS',
'MDSYS',
'OLAPSYS',
'MDDATA',
'SYSMAN',
'MGMT_VIEW',
'SCOTT');
数据据的查询,使用union合并两个表的内容,保证在check_job中出现的信息就会被查询出来SQL如下:
select host as 主机,
null as 厂区,
log_user as 用户,
job as job号,
null as 负任人,
null as JOB说明,
last_date as 上次执行时间,
next_date as 下次执行时间,
failures as 失败次数,
record_date as 点检时间
from check_job
where record_date > trunc(sysdate)
minus
select host,
null,
log_user,
check_job.job,
null,
null,
last_date,
next_date,
failures,
record_date
from check_job,job_detail
where check_job.host = job_detail.host
and check_job.job = job_detail.job
and record_date > trunc(sysdate)
union
select host,
factory,
log_user,
check_job.job,
who,
purpose,
last_date,
next_date,
failures,
record_date
from job_detail, check_job
where check_job.host = job_detail.host
and check_job.job = job_detail.job
and record_date > trunc(sysdate)
;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/7419833/viewspace-594928/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/7419833/viewspace-594928/