MoiaControl作业依赖关系查询语句

1 查看作业的上级作业及状态

/*
 查看作业的上级作业及作业状态
 8001：成功
 5001：失败
 1001：待处理
 4001：处理中
*/
select tt.job_name,
      tt.par_job_name,
      tt.lev,
      tt.isleaf,
      tt2.job_stat,
      tt2.detail_stat,
      tt2.stat_msg,
      tt2.redo_times
 From (select t.job_id,
              t.job_name,
              c.job_id as par_job_id,
              nvl(c.job_name, '0') as par_job_name,
              level lev,
              CONNECT_BY_ISLEAF ISLEAF
         From (select a.job_id, a.job_name, d.evt_id
                 from t04_job_info      a,
                      t04_node_info     b,
                      t04_evt_glob_rela c,
                      T04_EVT_GLOB_INFO d
                where a.job_id = b.obj_id(+)
                  and b.node_id = c.EVT_DES_ID(+)
                  and c.evt_id = d.evt_id(+)) t,
              t04_evt_glob_src a,
              t04_node_info b,
              t04_job_info c
        where t.evt_id = a.evt_id(+)
          and a.evt_src_id = b.node_id(+)
          and b.obj_id = c.job_id(+)
        start with t.job_name = 'XXXXXXXX'
       connect by prior nvl(c.job_name, '0') = t.job_name) tt,
      t05_job_stat tt2
where tt.par_job_id = tt2.job_id(+);

2 查看作业的下级作业及状态

/*
 查看作业的下级作业及作业状态
 8001：成功
 5001：失败
 1001：待处理
 4001：处理中
*/
select tt.par_job_name as job_name,
      tt.job_name as sub_job_name,
      tt.lev,
      tt.isleaf,
      tt2.job_stat,
      tt2.detail_stat,
      tt2.stat_msg,
      tt2.redo_times
 From (select t.job_id,
              t.job_name,
              c.job_id as par_job_id,
              nvl(c.job_name, '0') as par_job_name,
              level lev,
              CONNECT_BY_ISLEAF ISLEAF
         From (select a.job_id, a.job_name, d.evt_id
                 from t04_job_info      a,
                      t04_node_info     b,
                      t04_evt_glob_rela c,
                      T04_EVT_GLOB_INFO d
                where a.job_id = b.obj_id(+)
                  and b.node_id = c.EVT_DES_ID(+)
                  and c.evt_id = d.evt_id(+)) t,
              t04_evt_glob_src a,
              t04_node_info b,
              t04_job_info c
        where t.evt_id = a.evt_id(+)
          and a.evt_src_id = b.node_id(+)
          and b.obj_id = c.job_id(+)
        start with nvl(c.job_name, '0') = 'XXXXXXXX'
       connect by prior t.job_name = nvl(c.job_name, '0')) tt,
      t05_job_stat tt2
where tt.job_id = tt2.job_id(+);

3 查看作业的平级作业

/*
 查看作业的平级作业
*/
with t_t1 as (
select tt.job_id,
      tt.job_name,
      tt.par_job_id,
      tt.par_job_name,
      tt.lev,
      tt.isleaf
 From (select t.job_id,
              t.job_name,
              c.job_id as par_job_id,
              nvl(c.job_name, '0') as par_job_name,
              level lev,
              CONNECT_BY_ISLEAF ISLEAF
         From (select a.job_id, a.job_name, d.evt_id
                 from moiaee_bvt.t04_job_info      a,
                      moiaee_bvt.t04_node_info     b,
                      moiaee_bvt.t04_evt_glob_rela c,
                      moiaee_bvt.T04_EVT_GLOB_INFO d
                where a.job_id = b.obj_id(+)
                  and b.node_id = c.EVT_DES_ID(+)
                  and c.evt_id = d.evt_id(+)) t,
              moiaee_bvt.t04_evt_glob_src a,
              moiaee_bvt.t04_node_info b,
              moiaee_bvt.t04_job_info c
        where t.evt_id = a.evt_id(+)
          and a.evt_src_id = b.node_id(+)
          and b.obj_id = c.job_id(+)
        start with t.job_name = 'XXXXXXXX'
       connect by prior nvl(c.job_name, '0') = t.job_name) tt
where tt.lev = '1')
select tt.par_job_name as job_name,
      tt.job_name     as sub_job_name,
      tt.lev,
      tt.isleaf
 From (select t.job_id,
              t.job_name,
              c.job_id as par_job_id,
              nvl(c.job_name, '0') as par_job_name,
              level lev,
              CONNECT_BY_ISLEAF ISLEAF
         From (select a.job_id, a.job_name, d.evt_id
                 from moiaee_bvt.t04_job_info      a,
                      moiaee_bvt.t04_node_info     b,
                      moiaee_bvt.t04_evt_glob_rela c,
                      moiaee_bvt.T04_EVT_GLOB_INFO d
                where a.job_id = b.obj_id(+)
                  and b.node_id = c.EVT_DES_ID(+)
                  and c.evt_id = d.evt_id(+)) t,
              moiaee_bvt.t04_evt_glob_src a,
              moiaee_bvt.t04_node_info b,
              moiaee_bvt.t04_job_info c
        where t.evt_id = a.evt_id(+)
          and a.evt_src_id = b.node_id(+)
          and b.obj_id = c.job_id(+)
        start with nvl(c.job_name, '0') in (select par_job_name From t_t1 t1)
       connect by prior t.job_name = nvl(c.job_name, '0')) tt
where tt.lev = '1';

4 查看过程中依赖的表

select t.job_name as SP_NAME, nvl(c.job_name, '0') as YL_TABNAME, c.last_modify
 From (select a.job_id, a.job_name, d.evt_id
         from t04_job_info      a,
              t04_node_info     b,
              t04_evt_glob_rela c,
              T04_EVT_GLOB_INFO d
        where a.job_id = b.obj_id(+)
          and b.node_id = c.EVT_DES_ID(+)
          and c.evt_id = d.evt_id(+)) t,
      t04_evt_glob_src a,
      t04_node_info b,
      t04_job_info c
where t.evt_id = a.evt_id(+)
  and a.evt_src_id = b.node_id(+)
  and b.obj_id = c.job_id(+)
  and t.job_name like '%过程名称%';