VIEW: SYS.UTL_RECOMP_INVALID_MV

Source

View Text - Preformatted

WITH invalid_mvs_and_deps_base(obj_num, order_num) AS
       (SELECT obj#, 0
          FROM utl_recomp_all_objects
         WHERE type# = 42
           AND status IN (4, 5, 6)
        UNION ALL
        SELECT d.d_obj#, i.order_num + 1
          from dependency$ d, invalid_mvs_and_deps_base i
         where i.obj_num = d.p_obj#),
       invalid_mvs_and_dependents(obj_num, order_num) AS
       (SELECT obj_num, MAX(order_num)
          FROM invalid_mvs_and_deps_base
        GROUP BY obj_num
        ORDER BY MAX(order_num))
SELECT obj#, owner, objname, type#, namespace, status, edition_name
  FROM invalid_mvs_and_dependents, utl_recomp_all_objects urao
 WHERE obj_num = urao.obj#
   AND urao.type# = 42
   AND urao.obj# NOT IN (SELECT obj# FROM utl_recomp_compiled)
ORDER BY order_num

View Text - HTML Formatted

WITH INVALID_MVS_AND_DEPS_BASE(OBJ_NUM
, ORDER_NUM) AS (SELECT OBJ#
, 0
FROM UTL_RECOMP_ALL_OBJECTS
WHERE TYPE# = 42
AND STATUS IN (4
, 5
, 6) UNION ALL SELECT D.D_OBJ#
, I.ORDER_NUM + 1
FROM DEPENDENCY$ D
, INVALID_MVS_AND_DEPS_BASE I
WHERE I.OBJ_NUM = D.P_OBJ#)
, INVALID_MVS_AND_DEPENDENTS(OBJ_NUM
, ORDER_NUM) AS (SELECT OBJ_NUM
, MAX(ORDER_NUM)
FROM INVALID_MVS_AND_DEPS_BASE GROUP BY OBJ_NUM ORDER BY MAX(ORDER_NUM)) SELECT OBJ#
, OWNER
, OBJNAME
, TYPE#
, NAMESPACE
, STATUS
, EDITION_NAME
FROM INVALID_MVS_AND_DEPENDENTS
, UTL_RECOMP_ALL_OBJECTS URAO
WHERE OBJ_NUM = URAO.OBJ#
AND URAO.TYPE# = 42
AND URAO.OBJ# NOT IN (SELECT OBJ#
FROM UTL_RECOMP_COMPILED) ORDER BY ORDER_NUM