VIEW: SYS.USER_ILMDATAMOVEMENTPOLICIES

Source

View Text - Preformatted

select a.name,
       decode(b.action, 1,'COMPRESSION',2,'STORAGE'),
       decode(b.scope, 1,'SEGMENT',2,'GROUP',3,'ROW'),
       decode(b.ctype, 2, 'ADVANCED',
                       3, (CASE
                           WHEN b.clevel = 1 and bitand(b.flag,32) = 0
                                  THEN 'QUERY LOW'
                           WHEN b.clevel = 2 and bitand(b.flag,32) = 0
                                  THEN 'QUERY HIGH'
                           WHEN b.clevel = 3 and bitand(b.flag,32) = 0
                                  THEN 'ARCHIVE LOW'
                           WHEN b.clevel = 4 and bitand (b.flag,32) = 0
                                  THEN 'ARCHIVE HIGH'
                           WHEN b.clevel = 1 and bitand(b.flag,32)  = 32
                                  THEN 'QUERY LOW ROW LEVEL LOCKING'
                           WHEN b.clevel = 2 and bitand(b.flag,32)  = 32
                                  THEN 'QUERY HIGH ROW LEVEL LOCKING'
                           WHEN b.clevel = 3 and bitand(b.flag,32)  = 32
                                  THEN 'ARCHIVE LOW ROW LEVEL LOCKING'
                           WHEN b.clevel = 4 and bitand (b.flag,32) = 32
                                  THEN 'ARCHIVE HIGH ROW LEVEL LOCKING'
                           END)),
       b.tier_tbs,
       decode(b.flag, 1, 'READ ONLY'),
       CASE
       WHEN bitand(b.flag, 8) = 8 THEN 'USER DEFINED'
       WHEN b.action = 2 and  bitand(b.flag, 1) <> 1 THEN null
       ELSE
       decode(b.condition, 0, 'LAST ACCESS TIME', 1,'LOW ACCESS',
                           2,'LAST MODIFICATION TIME', 3,'CREATION TIME',
                           5, 'VALID TIME END')
       END,
       b.days,
       b.custfunc
  from sys.ilm$ a, sys.ilmpolicy$ b,
       (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 */)) c
 where a.policy# = b.policy#
   and a.policy# = c.policy#

View Text - HTML Formatted

SELECT A.NAME
, DECODE(B.ACTION
, 1
, 'COMPRESSION'
, 2
, 'STORAGE')
, DECODE(B.SCOPE
, 1
, 'SEGMENT'
, 2
, 'GROUP'
, 3
, 'ROW')
, DECODE(B.CTYPE
, 2
, 'ADVANCED'
, 3
, (CASE WHEN B.CLEVEL = 1
AND BITAND(B.FLAG
, 32) = 0 THEN 'QUERY LOW' WHEN B.CLEVEL = 2
AND BITAND(B.FLAG
, 32) = 0 THEN 'QUERY HIGH' WHEN B.CLEVEL = 3
AND BITAND(B.FLAG
, 32) = 0 THEN 'ARCHIVE LOW' WHEN B.CLEVEL = 4
AND BITAND (B.FLAG
, 32) = 0 THEN 'ARCHIVE HIGH' WHEN B.CLEVEL = 1
AND BITAND(B.FLAG
, 32) = 32 THEN 'QUERY LOW ROW LEVEL LOCKING' WHEN B.CLEVEL = 2
AND BITAND(B.FLAG
, 32) = 32 THEN 'QUERY HIGH ROW LEVEL LOCKING' WHEN B.CLEVEL = 3
AND BITAND(B.FLAG
, 32) = 32 THEN 'ARCHIVE LOW ROW LEVEL LOCKING' WHEN B.CLEVEL = 4
AND BITAND (B.FLAG
, 32) = 32 THEN 'ARCHIVE HIGH ROW LEVEL LOCKING' END))
, B.TIER_TBS
, DECODE(B.FLAG
, 1
, 'READ ONLY')
, CASE WHEN BITAND(B.FLAG
, 8) = 8 THEN 'USER DEFINED' WHEN B.ACTION = 2
AND BITAND(B.FLAG
, 1) <> 1 THEN NULL ELSE DECODE(B.CONDITION
, 0
, 'LAST ACCESS TIME'
, 1
, 'LOW ACCESS'
, 2
, 'LAST MODIFICATION TIME'
, 3
, 'CREATION TIME'
, 5
, 'VALID TIME END') END
, B.DAYS
, B.CUSTFUNC
FROM SYS.ILM$ A
, SYS.ILMPOLICY$ B
, (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 */)) C
WHERE A.POLICY# = B.POLICY#
AND A.POLICY# = C.POLICY#