为了更方便的监控数据库的运行状态,把数据库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)
  ;