VIEW: SYS.USER_ILMPOLICIES

Source

View Text - Preformatted

select  a.name,
        decode(a.ptype, 1, 'DATA MOVEMENT'),
        null,
        decode(bitand(a.FLAG,1),1,'NO',0,'YES'),
        decode(bitand(a.FLAG,64),0,'NO',64,'YES')
  from sys.ilm$ a
 where a.owner#   = userenv('SCHEMAID')
   and bitand(a.flag,8)     = 0
union
/* Select tablespace level policies in tablespaces where the user has some
 * quota
 */
select a.name,
       decode(a.ptype, 1, 'DATA MOVEMENT'),
       b.name,
       decode(bitand(a.FLAG,1),1,'NO',0,'YES'),
       decode(bitand(a.FLAG,64),0,'NO',64,'YES')
  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 tablespace level policies in case the use has unlimited
 * tablespace privileges
 */
select a.name,
       decode(a.ptype, 1, 'DATA MOVEMENT'),
       b.name,
       decode(bitand(a.FLAG,1),1,'NO',0,'YES'),
       decode(bitand(a.FLAG,64),0,'NO',64,'YES')
  from sys.ilm$ a,
       sys.ts$  b
 where bitand(a.flag,8) = 8
   and a.ts# = b.ts#
   and exists
      (select null
         from sys.v$enabledprivs
        where priv_number = -15 /* UNLIMITED TABLESPACE */)

View Text - HTML Formatted

SELECT A.NAME
, DECODE(A.PTYPE
, 1
, 'DATA MOVEMENT')
, NULL
, DECODE(BITAND(A.FLAG
, 1)
, 1
, 'NO'
, 0
, 'YES')
, DECODE(BITAND(A.FLAG
, 64)
, 0
, 'NO'
, 64
, 'YES')
FROM SYS.ILM$ A
WHERE A.OWNER# = USERENV('SCHEMAID')
AND BITAND(A.FLAG
, 8) = 0 UNION /* SELECT TABLESPACE LEVEL POLICIES IN TABLESPACES
WHERE THE USER HAS SOME * QUOTA */ SELECT A.NAME
, DECODE(A.PTYPE
, 1
, 'DATA MOVEMENT')
, B.NAME
, DECODE(BITAND(A.FLAG
, 1)
, 1
, 'NO'
, 0
, 'YES')
, DECODE(BITAND(A.FLAG
, 64)
, 0
, 'NO'
, 64
, 'YES')
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 TABLESPACE LEVEL POLICIES IN CASE THE USE HAS UNLIMITED * TABLESPACE PRIVILEGES */ SELECT A.NAME
, DECODE(A.PTYPE
, 1
, 'DATA MOVEMENT')
, B.NAME
, DECODE(BITAND(A.FLAG
, 1)
, 1
, 'NO'
, 0
, 'YES')
, DECODE(BITAND(A.FLAG
, 64)
, 0
, 'NO'
, 64
, 'YES')
FROM SYS.ILM$ A
, SYS.TS$ B
WHERE BITAND(A.FLAG
, 8) = 8
AND A.TS# = B.TS#
AND EXISTS (SELECT NULL
FROM SYS.V$ENABLEDPRIVS
WHERE PRIV_NUMBER = -15 /* UNLIMITED TABLESPACE */)