VIEW: SYS.USER_ILMOBJECTS

Source

View Text - Preformatted

select a.name, d.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$ d,
       (select a.policy#
          from sys.ilm$ a
         where bitand(a.flag, 8) = 0
           and a.owner# = userenv('SCHEMAID')
        union
        select a.policy#
          from sys.ilm$ a ,
               sys.ts$  b,
               sys.tsq$ c
        where  bitand(a.flag, 8) = 8
          and  a.ts#    = b.ts#
          and  b.ts#    = c.ts#
          and  c.user# = userenv('SCHEMAID')
          and (c.blocks > 0 or c.maxblocks != 0)
        union
         select a.policy#
          from sys.ilm$ a
        where  bitand(a.flag, 8) = 8
          and  exists
         (select null
            from sys.v$enabledprivs
           where priv_number = -15 /* UNLIMITED TABLESPACE */)) e,
       sys.ts$ f
 where a.policy# = b.policy#
   AND b.obj#    = c.obj#
   AND c.owner#  = d.user#
   AND a.policy# = e.policy#
   AND c.name not like 'REDEF$%'
   AND c.name not like 'MLOG$%'
   AND f.ts# (+) = a.ts#

View Text - HTML Formatted

SELECT A.NAME
, D.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$ D
, (SELECT A.POLICY#
FROM SYS.ILM$ A
WHERE BITAND(A.FLAG
, 8) = 0
AND A.OWNER# = USERENV('SCHEMAID') UNION SELECT A.POLICY#
FROM SYS.ILM$ A
, SYS.TS$ B
, SYS.TSQ$ C
WHERE BITAND(A.FLAG
, 8) = 8
AND A.TS# = B.TS#
AND B.TS# = C.TS#
AND C.USER# = USERENV('SCHEMAID')
AND (C.BLOCKS > 0 OR C.MAXBLOCKS != 0) UNION SELECT A.POLICY#
FROM SYS.ILM$ A
WHERE BITAND(A.FLAG
, 8) = 8
AND EXISTS (SELECT NULL
FROM SYS.V$ENABLEDPRIVS
WHERE PRIV_NUMBER = -15 /* UNLIMITED TABLESPACE */)) E
, SYS.TS$ F
WHERE A.POLICY# = B.POLICY#
AND B.OBJ# = C.OBJ#
AND C.OWNER# = D.USER#
AND A.POLICY# = E.POLICY#
AND C.NAME NOT LIKE 'REDEF$%'
AND C.NAME NOT LIKE 'MLOG$%'
AND F.TS# (+) = A.TS#