VIEW: SYS.DBA_ILMOBJECTS

Source

View Text - Preformatted

select a.name, e.name, c.name, c.subname,
       DECODE(c.type#,19, 'TABLE PARTITION',
                      2,'TABLE',
                      34,'TABLE SUBPARTITION'),
       (CASE
        WHEN bitand(a.FLAG,8) = 8
             THEN 'TABLESPACE'
        WHEN (b.obj_typ <> b.obj_typ_orig AND b.obj_typ_orig = 2)
             THEN 'TABLE'
        WHEN (b.obj_typ <> b.obj_typ_orig AND b.obj_typ_orig = 19)
             THEN 'TABLE PARTITION'
        ELSE 'POLICY NOT INHERITED'
        END),
       f.name,
       (CASE
        WHEN (bitand(b.FLAG,1)    = 1)
             THEN 'NO'
        ELSE 'YES'
        END),
       (CASE
        WHEN (bitand(b.FLAG,64)    = 0)
             THEN 'NO'
        ELSE 'YES'
        END)
  from sys.ilm$ a,
       sys.ilmobj$ b,
       sys.obj$ c,
       sys.user$ e,
       sys.ts$ f
 where a.policy# = b.policy#
   AND b.obj#    = c.obj#
   AND c.owner#  = e.user#
   and c.name not like 'REDEF$%'
   and c.name not like 'MLOG$%'
   and f.ts# (+) = a.ts#

View Text - HTML Formatted

SELECT A.NAME
, E.NAME
, C.NAME
, C.SUBNAME
, DECODE(C.TYPE#
, 19
, 'TABLE PARTITION'
, 2
, 'TABLE'
, 34
, 'TABLE SUBPARTITION')
, (CASE WHEN BITAND(A.FLAG
, 8) = 8 THEN 'TABLESPACE' WHEN (B.OBJ_TYP <> B.OBJ_TYP_ORIG
AND B.OBJ_TYP_ORIG = 2) THEN 'TABLE' WHEN (B.OBJ_TYP <> B.OBJ_TYP_ORIG
AND B.OBJ_TYP_ORIG = 19) THEN 'TABLE PARTITION' ELSE 'POLICY NOT INHERITED' END)
, F.NAME
, (CASE WHEN (BITAND(B.FLAG
, 1) = 1) THEN 'NO' ELSE 'YES' END)
, (CASE WHEN (BITAND(B.FLAG
, 64) = 0) THEN 'NO' ELSE 'YES' END)
FROM SYS.ILM$ A
, SYS.ILMOBJ$ B
, SYS.OBJ$ C
, SYS.USER$ E
, SYS.TS$ F
WHERE A.POLICY# = B.POLICY#
AND B.OBJ# = C.OBJ#
AND C.OWNER# = E.USER#
AND C.NAME NOT LIKE 'REDEF$%'
AND C.NAME NOT LIKE 'MLOG$%'
AND F.TS# (+) = A.TS#