Infa元数据查询

--查询使用了哪些onnections --only reusable session

select DISTINCT CONNECTION_NAME from  

REP_SESSION_CNXSwhere SUBJECT_AREA='DW_ORACLE_ETL' 

select*from REP_SESSION_CNXS where connection_name='mysql_product_single'

--check Custom_MFE_To_Analytics_SyncData workflow if on sechedule

select *from OPB_WFLOW_RUN r where--run_type=1

 r.workflow_name  ='wf_DW_SDESIL_HotelOrderFact'

select distincttrunc(r.start_time)from OPB_WFLOW_RUN r where run_type=1

and r.workflow_name='Custom_MFE_To_Analytics_SyncData'

order bytrunc(r.start_time)desc

select workflow_name,max(r.start_time)from OPB_WFLOW_RUN r  where run_type=1

groupby workflow_nameorderby 2 desc

select workflow_name, r.start_timefrom OPB_WFLOW_RUN r where run_type=1orderby 1,2desc

select workflow_name,trunc(r.start_time),count(*)from OPB_WFLOW_RUN r  where run_type=1 

groupby workflow_name,trunc(r.start_time)

order by1,2desc

--schedule运行失败

select* from informatica_etl_daily.OPB_WFLOW_RUN r where run_type=1

and r.workflow_name='WF_ETL_SQL_LOG'and

 to_number(to_char(sysdate,'hh24'))>1and start_time<sysdateand start_time>sysdate-1

--运行失败

select v.Server_Cat,v.user_name,v.workflow_name,v.start_time,v.end_time,v.run_type,v.run_err_msg,v.subject_area

 from (select'INFA_24' as Server_Cat,

               r2.user_name,

               r2.workflow_name,

               r2.start_time,

               r2.end_time,

               r2.run_status_code,

              case when r2.run_type=1then '调度 when r2.run_type=4then '服务重启自动跑else '手动 end as run_type,

               r2.run_err_msg,

               r2.subject_area,

               ROW_NUMBER()over(partitionby r2.workflow_id order by r2.start_time desc) as max_time

         from INFORMATICA_ETL.REP_WFLOW_RUN r2) v

 where v.max_time= 1

  and v.start_time >=trunc(sysdate)- 3

  and v.run_status_code = 3

--查询某个select语句

select * from   INFORMATICA_ETL.OPB_WIDGET_ATTR where lower(attr_value)  like '%air3_sql%' 

select * from INFORMATICA_ETL.OPB_SWIDGET_ATTR a where lower(attr_value) like '%air3_sql%'

select w.workflow_name,r.task_name,r.instance_name 

from  informatica_etl.OPB_TASK_INST_RUN  r,informatica_etl.OPB_WFLOW_RUN  w

 where r.workflow_run_id=w.workflow_run_id

 and r.task_id=1830 --OPB_SWIDGET_ATTR.session_id

and r.start_time>=trunc(sysdate)

--DAC报错

select *

from 

(select'24DAC',q.last_upd start_time,q.etl_defn_name,q.status,q.queued_steps,q.failed_steps,q.etl_proc_widfrom 

(select * from DAC_ETL.W_ETL_DEFN_RUN rwhere r.last_upd>=sysdate-3

order by r.start_tsdesc ) qwhere rownum<=1

)wwhere w.start_time<sysdate-0.08

select 'DAC_ETL_DAILY',

       q.last_upd start_time,

       q.etl_defn_name,

       q.status,

       q.queued_steps,

       q.failed_steps,

       q.etl_proc_wid

  from (select*

          from DAC_ETL_DAILY.W_ETL_DEFN_RUN r

         where r.last_upd >= sysdate - 7

           and status= 'Failed'--not in ('Completed','Running') --and etl_defn_name='EP-ODS Daily'

        union all

        select *

          from DAC_ETL_DAILY.W_ETL_DEFN_RUN r1

         where r1.last_upd >= sysdate - 7

           and r1.status = 'Running'

           and r1.last_upd < sysdate - 1

        /*order by start_ts desc*/

        ) q 

-- 对应到ETL INFA

select 'DAC',

       r.last_upd       执行计划开始时间,

       r.etl_defn_name  执行计划名称,

       r.etl_proc_wid   执行计划实例D,

       r.status         执行计划状态,

       r.queued_steps   排队任务数,

       r.failed_steps   失败任务数,

       s.step_name      任务名称,

       s.step_wid       任务示例D,

       s.start_ts       任务开始时间,

       s.status         任务状态,

       s.status_desc    任务状态描述,

       s.exec_type_name,

       p.cmd_name,

       p.fl_cmd_name

  from (select *

          from (select *

                  from DAC_ETL.W_ETL_DEFN_RUN r

                 where r.last_upd >= sysdate - 3

                 order by r.start_ts desc) q

         where rownum <= 1) r,

       DAC_ETL.W_ETL_RUN_STEP s,

       DAC_ETL.W_ETL_DEFN_STEP k,

       DAC_ETL.W_ETL_STEP p

 where r.row_wid = s.run_wid

   and s.defn_step_wid = k.row_wid

   and k.step_wid=p.row_wid