VIEW: SYS.DBA_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
 where a.policy# = b.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
WHERE A.POLICY# = B.POLICY#