eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: SYS.DBA_TAB_PARTITIONS

Source

View Text - Preformatted


select u.name, o.name, 'NO', o.subname, 0,
       tp.hiboundval, tp.hiboundlen,
       row_number() over (partition by u.name, o.name order by tp.part#),
       ts.name, tp.pctfree$,
       decode(bitand(ts.flags, 32), 32, to_number(NULL), tp.pctused$),
       tp.initrans, tp.maxtrans,
       decode(bitand(tp.flags, 65536), 65536,
              ds.initial_stg * ts.blocksize, s.iniexts * ts.blocksize),
       decode(bitand(tp.flags, 65536), 65536,
              ds.next_stg * ts.blocksize, s.extsize * ts.blocksize),
       decode(bitand(tp.flags, 65536), 65536, ds.minext_stg, s.minexts),
       decode(bitand(tp.flags, 65536), 65536, ds.maxext_stg, s.maxexts),
       decode(bitand(tp.flags, 65536), 65536,
              ds.maxsiz_stg * ts.blocksize,
              decode(bitand(s.spare1, 4194304), 4194304, bitmapranges, NULL)),
       decode(bitand(ts.flags, 3), 1, to_number(NULL),
              decode(bitand(tp.flags, 65536), 65536, ds.pctinc_stg, s.extpct)),
       decode(bitand(ts.flags, 32), 32, to_number(NULL),
              decode(bitand(tp.flags, 65536), 65536,
                     decode(ds.frlins_stg, 0, 1, ds.frlins_stg),
                     decode(s.lists, 0, 1, s.lists))),
       decode(bitand(ts.flags, 32), 32, to_number(NULL),
              decode(bitand(tp.flags, 65536), 65536,
                     decode(ds.maxins_stg, 0, 1, ds.maxins_stg),
                     decode(s.groups, 0, 1, s.groups))),
       decode(mod(trunc(tp.flags / 4), 2), 0, 'YES', 'NO'),
       case when (bitand(tp.flags, 65536) = 65536) then
         decode(bitand(ds.flags_stg, 4), 4, 'ENABLED', 'DISABLED')
       else
         decode(bitand(s.spare1, 2048), 2048, 'ENABLED', 'DISABLED')
       end,
       case when (bitand(tp.flags, 65536) = 65536) then
          decode(bitand(ds.flags_stg, 4), 4,
          case when bitand(ds.cmpflag_stg, 3) = 1 then 'BASIC'
               when bitand(ds.cmpflag_stg, 3) = 2 then 'ADVANCED'
               else concat(decode(ds.cmplvl_stg, 1, 'QUERY LOW',
                                                 2, 'QUERY HIGH',
                                                 3, 'ARCHIVE LOW',
                                                    'ARCHIVE HIGH'),
                           decode(bitand(ds.flags_stg, 524288), 524288,
                                  ' ROW LEVEL LOCKING', '')) end,
           null)
       else
         decode(bitand(s.spare1, 2048), 0, null,
           case when bitand(s.spare1, 16777216) = 16777216
                     then 'ADVANCED'
                when bitand(s.spare1, 100663296) = 33554432  -- 0x2000000
                     then concat('QUERY LOW',
                                 decode(bitand(s.spare1, 2147483648),
                                        2147483648, ' ROW LEVEL LOCKING', ''))
                when bitand(s.spare1, 100663296) = 67108864  -- 0x4000000
                     then concat('QUERY HIGH',
                                 decode(bitand(s.spare1, 2147483648),
                                        2147483648, ' ROW LEVEL LOCKING', ''))
                when bitand(s.spare1, 100663296) = 100663296 -- 0x2000000+0x4000000
                     then concat('ARCHIVE LOW',
                                 decode(bitand(s.spare1, 2147483648),
                                        2147483648, ' ROW LEVEL LOCKING', ''))
                when bitand(s.spare1, 134217728) = 134217728 -- 0x8000000
                     then concat('ARCHIVE HIGH',
                                 decode(bitand(s.spare1, 2147483648),
                                        2147483648, ' ROW LEVEL LOCKING', ''))
                else 'BASIC' end)
       end,
       tp.rowcnt, tp.blkcnt, tp.empcnt, tp.avgspc, tp.chncnt, tp.avgrln,
       tp.samplesize, tp.analyzetime,
       decode(bitand(decode(bitand(tp.flags, 65536), 65536, ds.bfp_stg, s.cachehint), 3),
              1, 'KEEP', 2, 'RECYCLE', 'DEFAULT'),
       decode(bitand(decode(bitand(tp.flags, 65536), 65536, ds.bfp_stg, s.cachehint), 12)/4,
              1, 'KEEP', 2, 'NONE', 'DEFAULT'),
       decode(bitand(decode(bitand(tp.flags, 65536), 65536, ds.bfp_stg, s.cachehint), 48)/16,
              1, 'KEEP', 2, 'NONE', 'DEFAULT'),
       decode(bitand(tp.flags, 16), 0, 'NO', 'YES'),
       decode(bitand(tp.flags, 8), 0, 'NO', 'YES'),
       decode(bitand(o.flags, 16384), 0, 'NO', 'YES'),
       case bitand(o.flags, 16384)   --is the object a nested table partition?
       when  16384 then
       (select o1.subname
        from obj$ o1
        where o1.obj#=
        (select tp1.obj#
         from tabpartv$ tp1, tabpartv$ tp2, ntab$ nt
         where tp2.bo# = tp.bo#
         and tp2.obj# = tp.obj#
         and tp1.part# = tp2.part#
         and tp1.bo#=nt.obj#
         and nt.ntab#=tp.bo#))
       else
         null
       end,
       decode(bitand(tp.flags, 32768), 32768, 'YES', 'NO'),
       decode(bitand(tp.flags, 65536), 65536, 'NO', 'YES'),
       decode(bitand(tp.flags, 2097152), 2097152, 'OFF', 'ON'),
       --INMEMORY
       case when (bitand(tp.flags, 65536) = 65536) then
          -- flags/imcflag_stg (stgdef.h
          decode(bitand(ds.flags_stg, 6291456),
                2097152, 'ENABLED',
                4194304, 'DISABLED', 'DISABLED')
       else
          -- ktsscflg (ktscts.h)
          decode(bitand(s.spare1, 70373039144960),
                4294967296, 'ENABLED',
                70368744177664, 'DISABLED', 'DISABLED')
       end,
       -- INMEMORY_PRIORITY
       case when (bitand(tp.flags, 65536) = 65536) then
         decode(bitand(ds.flags_stg, 2097152), 2097152,
                decode(bitand(ds.imcflag_stg, 4), 4,
                decode(bitand(ds.flags_stg, 2097152), 2097152,
                decode(bitand(ds.imcflag_stg, 7936),
                256, 'NONE',
                512, 'LOW',
                1024, 'MEDIUM',
                2048, 'HIGH',
                4096, 'CRITICAL', 'UNKNOWN'), null),
                'NONE'),
                null)
       else
         decode(bitand(s.spare1, 4294967296), 4294967296,
                decode(bitand(s.spare1, 34359738368), 34359738368,
                decode(bitand(s.spare1, 61572651155456),
                8796093022208, 'LOW',
                17592186044416, 'MEDIUM',
                35184372088832, 'HIGH',
                52776558133248, 'CRITICAL', 'NONE'),
                'NONE'),
                null)
       end,
       -- INMEMORY_DISTRIBUTE
       case when (bitand(tp.flags, 65536) = 65536) then
         decode(bitand(ds.flags_stg, 2097152), 2097152,
                decode(bitand(ds.imcflag_stg, 1), 1,
                       decode(bitand(ds.imcflag_stg, (16+32)),
                              16,  'BY ROWID RANGE',
                              32,  'BY PARTITION',
                              48,  'BY SUBPARTITION',
                               0,  'AUTO'),
                  null), null)
       else
         decode(bitand(s.spare1, 4294967296), 4294967296,
               decode(bitand(s.spare1, 8589934592), 8589934592,
                        decode(bitand(s.spare1, 206158430208),
                        68719476736,   'BY ROWID RANGE',
                        137438953472,  'BY PARTITION',
                        206158430208,  'BY SUBPARTITION',
                        0,             'AUTO'),
                        null),
                  null)
       end,
       -- INMEMORY_COMPRESSION
       case when (bitand(tp.flags, 65536) = 65536) then
        decode(bitand(ds.flags_stg, 2097152), 2097152,
               decode(bitand(ds.imcflag_stg, (2+8+64+128)),
                              2,   'NO MEMCOMPRESS',
                              8,  'FOR DML',
                              10,  'FOR QUERY LOW',
                              64, 'FOR QUERY HIGH',
                              66, 'FOR CAPACITY LOW',
                              72, 'FOR CAPACITY HIGH', 'UNKNOWN'),
                 null)
       else
         decode(bitand(s.spare1, 4294967296), 4294967296,
                decode(bitand(s.spare1, 841813590016),
                              17179869184,  'NO MEMCOMPRESS',
                              274877906944, 'FOR DML',
                              292057776128, 'FOR QUERY LOW',
                              549755813888, 'FOR QUERY HIGH',
                              566935683072, 'FOR CAPACITY LOW',
                              824633720832, 'FOR CAPACITY HIGH', 'UNKNOWN'),
                 null)
       end,
       -- INMEMORY_DUPLICATE
       case when (bitand(tp.flags, 65536) = 65536) then
        decode(bitand(ds.flags_stg, 2097152), 2097152,
               decode(bitand(ds.imcflag_stg, (8192+16384)),
                              8192,   'NO DUPLICATE',
                              16384,  'DUPLICATE',
                              24576,  'DUPLICATE ALL',
                              null),
                null)
       else
          decode(bitand(s.spare1, 4294967296), 4294967296,
                   decode(bitand(s.spare1, 6597069766656),
                           2199023255552, 'NO DUPLICATE',
                           4398046511104, 'DUPLICATE',
                           6597069766656, 'DUPLICATE ALL', null),
                null)
       end
from   obj$ o, tabpart$ tp, ts$ ts, sys.seg$ s, user$ u, tab$ t,
       sys.deferred_stg$ ds
where  o.obj# = tp.obj# and ts.ts# = tp.ts# and u.user# = o.owner# and
       tp.obj# = ds.obj#(+) and
       tp.file#=s.file#(+) and tp.block#=s.block#(+) and tp.ts#=s.ts#(+) and
       bitand(t.property, 64) != 64 and
       bitand(tp.flags, 8388608) = 0 and   /* filter out hidden partitions */
       tp.bo# = t.obj# and bitand(t.trigflag, 1073741824) != 1073741824
       and o.namespace = 1 and o.remoteowner IS NULL and o.linkname IS NULL
union all -- IOT PARTITIONS
select u.name, o.name, 'NO', o.subname, 0,
       tp.hiboundval, tp.hiboundlen,
       row_number() over (partition by u.name, o.name order by tp.part#), NULL,
       TO_NUMBER(NULL),TO_NUMBER(NULL),TO_NUMBER(NULL),TO_NUMBER(NULL),
       TO_NUMBER(NULL), TO_NUMBER(NULL),
       TO_NUMBER(NULL),TO_NUMBER(NULL),TO_NUMBER(NULL),TO_NUMBER(NULL),
       TO_NUMBER(NULL),TO_NUMBER(NULL),
       NULL,
       'N/A', 'N/A',
       tp.rowcnt, TO_NUMBER(NULL), TO_NUMBER(NULL), 0, tp.chncnt, tp.avgrln,
       tp.samplesize, tp.analyzetime, NULL, NULL, NULL,
       decode(bitand(tp.flags, 16), 0, 'NO', 'YES'),
       decode(bitand(tp.flags, 8), 0, 'NO', 'YES'),
       'N/A', 'N/A', 'N/A',
       decode(bitand(tp.flags, 65536), 65536, 'NO', 'YES'),
       decode(bitand(tp.flags, 2097152), 2097152, 'OFF', 'ON'),
       'N/A', 'N/A', 'N/A', 'N/A', 'N/A'
from   obj$ o, tabpart$ tp, user$ u, tab$ t
where  o.obj# = tp.obj# and o.owner# = u.user# and
       tp.bo# = t.obj# and bitand(t.property, 64) = 64 and
       bitand(t.trigflag, 1073741824) != 1073741824
       and o.namespace = 1 and o.remoteowner IS NULL and o.linkname IS NULL
union all -- COMPOSITE PARTITIONS
select u.name, o.name, 'YES', o.subname, tcp.subpartcnt,
       tcp.hiboundval, tcp.hiboundlen,
       row_number() over (partition by u.name, o.name order by tcp.part#),
       ts.name,
       tcp.defpctfree, decode(bitand(ts.flags, 32), 32, to_number(NULL),
       tcp.defpctused),
       tcp.definitrans, tcp.defmaxtrans,
       tcp.definiexts, tcp.defextsize, tcp.defminexts, tcp.defmaxexts,
       tcp.defmaxsize, tcp.defextpct,
       decode(bitand(ts.flags, 32), 32, to_number(NULL), tcp.deflists),
       decode(bitand(ts.flags, 32), 32, to_number(NULL), tcp.defgroups),
       decode(tcp.deflogging, 0, 'NONE', 1, 'YES', 2, 'NO', 'UNKNOWN'),
       decode(bitand(tcp.spare2, 3), 1, 'ENABLED', 2, 'DISABLED', 'NONE'),
       decode(bitand(tcp.spare2, 1), 0, null,
         case bitand(tcp.spare2, 63) -- 1st 6 bits used
         when 1 then 'BASIC'                             -- 00000001
         when 5 then 'ADVANCED'                          -- 00000101
         when 9 then 'QUERY LOW'                         -- 00001001
         when 17 then 'QUERY HIGH'                       -- 00010001
         when 25 then 'ARCHIVE LOW'                      -- 00011001
         when 33 then 'ARCHIVE HIGH'                     -- 00100001
         when 73 then 'QUERY LOW ROW LEVEL LOCKING'      -- 01001001
         when 81 then 'QUERY HIGH ROW LEVEL LOCKING'     -- 01010001
         when 89 then 'ARCHIVE LOW ROW LEVEL LOCKING'    -- 01011001
         when 97 then 'ARCHIVE HIGH ROW LEVEL LOCKING'   -- 01100001
                 else 'UNKNOWN' end),                    -- internal ilevels
       tcp.rowcnt, tcp.blkcnt, tcp.empcnt, tcp.avgspc, tcp.chncnt, tcp.avgrln,
       tcp.samplesize, tcp.analyzetime,
       decode(bitand(tcp.defbufpool, 3), 1, 'KEEP', 2, 'RECYCLE', 'DEFAULT'),
       decode(bitand(tcp.defbufpool, 12)/4, 1, 'KEEP', 2, 'NONE', 'DEFAULT'),
       decode(bitand(tcp.defbufpool, 48)/16, 1, 'KEEP', 2, 'NONE', 'DEFAULT'),
       decode(bitand(tcp.flags, 16), 0, 'NO', 'YES'),
       decode(bitand(tcp.flags, 8), 0, 'NO', 'YES'),
       'N/A', 'N/A',
       decode(bitand(tcp.flags, 32768), 32768, 'YES', 'NO'),
       decode(bitand(tcp.spare2, 768), 256, 'NO', 512, 'YES', 'NONE'),
       decode(bitand(tcp.spare2, 3072), 1024, 'ON', 2048, 'OFF', 'NONE'),
       -- INMEMORY
       -- defimcflags_kkpacpcd
       decode(bitand(tcp.spare2, 196608),
              65536,  'ENABLED',
              131072, 'DISABLED', 'DISABLED'),
       -- INMEMORY_PRIORITY
       decode(bitand(tcp.spare2, 65536), 65536,
              decode(bitand(tcp.spare2, 1048576), 1048576,
              decode(bitand(tcp.spare2, 1879048192)/268435456,
              0, 'NONE',
              1, 'LOW',
              2, 'MEDIUM',
              3, 'HIGH',
              4, 'CRITICAL', 'UNKNOWN'), 'NONE'), null),
       -- INMEMORY_DISTRIBUTE
       decode(bitand(tcp.spare2, 65536), 65536,
              decode(bitand(tcp.spare2, 262144), 262144,
                     decode(bitand(tcp.spare2, (2097152+4194304)),
                     0,         'AUTO',
                     2097152,   'BY ROWID RANGE',
                     4194304,   'BY PARTITION',
                     6291456,   'BY SUBPARTITION'),
              null), null),
       -- INMEMORY_COMPRESSION
       decode(bitand(tcp.spare2, 65536), 65536,
              decode(bitand(tcp.spare2, (524288+8388608+16777216)),
                              524288,             'NO MEMCOMPRESS',
                              8388608,            'FOR DML',
                              (524288+8388608),   'FOR QUERY LOW',
                              16777216,           'FOR QUERY HIGH',
                              (524288+16777216),  'FOR CAPACITY LOW',
                              (8388608+16777216), 'FOR CAPACITY HIGH',
                     null),
              null),
       -- INMEMORY_DUPLICATE
       decode (bitand(tcp.spare2, 65536), 65536,
               decode(bitand(tcp.spare2, (67108864+134217728)),
                              67108864,   'NO DUPLICATE',
                              134217728,  'DUPLICATE',
                              (67108864+134217728),  'DUPLICATE ALL',
                              null),
                null)
from   obj$ o, tabcompart$ tcp, ts$ ts, user$ u, tab$ t
where  o.obj# = tcp.obj# and tcp.defts# = ts.ts# and u.user# = o.owner# and
       tcp.bo# = t.obj#
       and bitand(t.trigflag, 1073741824) != 1073741824
       and bitand(t.property, 64) != 64
       and bitand(tcp.flags, 8388608) = 0    /* filter out hidden partitions */
       and o.namespace = 1 and o.remoteowner IS NULL and o.linkname IS NULL

View Text - HTML Formatted

SELECT U.NAME
, O.NAME
, 'NO'
, O.SUBNAME
, 0
, TP.HIBOUNDVAL
, TP.HIBOUNDLEN
, ROW_NUMBER() OVER (PARTITION BY U.NAME
, O.NAME ORDER BY TP.PART#)
, TS.NAME
, TP.PCTFREE$
, DECODE(BITAND(TS.FLAGS
, 32)
, 32
, TO_NUMBER(NULL)
, TP.PCTUSED$)
, TP.INITRANS
, TP.MAXTRANS
, DECODE(BITAND(TP.FLAGS
, 65536)
, 65536
, DS.INITIAL_STG * TS.BLOCKSIZE
, S.INIEXTS * TS.BLOCKSIZE)
, DECODE(BITAND(TP.FLAGS
, 65536)
, 65536
, DS.NEXT_STG * TS.BLOCKSIZE
, S.EXTSIZE * TS.BLOCKSIZE)
, DECODE(BITAND(TP.FLAGS
, 65536)
, 65536
, DS.MINEXT_STG
, S.MINEXTS)
, DECODE(BITAND(TP.FLAGS
, 65536)
, 65536
, DS.MAXEXT_STG
, S.MAXEXTS)
, DECODE(BITAND(TP.FLAGS
, 65536)
, 65536
, DS.MAXSIZ_STG * TS.BLOCKSIZE
, DECODE(BITAND(S.SPARE1
, 4194304)
, 4194304
, BITMAPRANGES
, NULL))
, DECODE(BITAND(TS.FLAGS
, 3)
, 1
, TO_NUMBER(NULL)
, DECODE(BITAND(TP.FLAGS
, 65536)
, 65536
, DS.PCTINC_STG
, S.EXTPCT))
, DECODE(BITAND(TS.FLAGS
, 32)
, 32
, TO_NUMBER(NULL)
, DECODE(BITAND(TP.FLAGS
, 65536)
, 65536
, DECODE(DS.FRLINS_STG
, 0
, 1
, DS.FRLINS_STG)
, DECODE(S.LISTS
, 0
, 1
, S.LISTS)))
, DECODE(BITAND(TS.FLAGS
, 32)
, 32
, TO_NUMBER(NULL)
, DECODE(BITAND(TP.FLAGS
, 65536)
, 65536
, DECODE(DS.MAXINS_STG
, 0
, 1
, DS.MAXINS_STG)
, DECODE(S.GROUPS
, 0
, 1
, S.GROUPS)))
, DECODE(MOD(TRUNC(TP.FLAGS / 4)
, 2)
, 0
, 'YES'
, 'NO')
, CASE WHEN (BITAND(TP.FLAGS
, 65536) = 65536) THEN DECODE(BITAND(DS.FLAGS_STG
, 4)
, 4
, 'ENABLED'
, 'DISABLED') ELSE DECODE(BITAND(S.SPARE1
, 2048)
, 2048
, 'ENABLED'
, 'DISABLED') END
, CASE WHEN (BITAND(TP.FLAGS
, 65536) = 65536) THEN DECODE(BITAND(DS.FLAGS_STG
, 4)
, 4
, CASE WHEN BITAND(DS.CMPFLAG_STG
, 3) = 1 THEN 'BASIC' WHEN BITAND(DS.CMPFLAG_STG
, 3) = 2 THEN 'ADVANCED' ELSE CONCAT(DECODE(DS.CMPLVL_STG
, 1
, 'QUERY LOW'
, 2
, 'QUERY HIGH'
, 3
, 'ARCHIVE LOW'
, 'ARCHIVE HIGH')
, DECODE(BITAND(DS.FLAGS_STG
, 524288)
, 524288
, ' ROW LEVEL LOCKING'
, '')) END
, NULL) ELSE DECODE(BITAND(S.SPARE1
, 2048)
, 0
, NULL
, CASE WHEN BITAND(S.SPARE1
, 16777216) = 16777216 THEN 'ADVANCED' WHEN BITAND(S.SPARE1
, 100663296) = 33554432 -- 0X2000000 THEN CONCAT('QUERY LOW'
, DECODE(BITAND(S.SPARE1
, 2147483648)
, 2147483648
, ' ROW LEVEL LOCKING'
, '')) WHEN BITAND(S.SPARE1
, 100663296) = 67108864 -- 0X4000000 THEN CONCAT('QUERY HIGH'
, DECODE(BITAND(S.SPARE1
, 2147483648)
, 2147483648
, ' ROW LEVEL LOCKING'
, '')) WHEN BITAND(S.SPARE1
, 100663296) = 100663296 -- 0X2000000+0X4000000 THEN CONCAT('ARCHIVE LOW'
, DECODE(BITAND(S.SPARE1
, 2147483648)
, 2147483648
, ' ROW LEVEL LOCKING'
, '')) WHEN BITAND(S.SPARE1
, 134217728) = 134217728 -- 0X8000000 THEN CONCAT('ARCHIVE HIGH'
, DECODE(BITAND(S.SPARE1
, 2147483648)
, 2147483648
, ' ROW LEVEL LOCKING'
, '')) ELSE 'BASIC' END) END
, TP.ROWCNT
, TP.BLKCNT
, TP.EMPCNT
, TP.AVGSPC
, TP.CHNCNT
, TP.AVGRLN
, TP.SAMPLESIZE
, TP.ANALYZETIME
, DECODE(BITAND(DECODE(BITAND(TP.FLAGS
, 65536)
, 65536
, DS.BFP_STG
, S.CACHEHINT)
, 3)
, 1
, 'KEEP'
, 2
, 'RECYCLE'
, 'DEFAULT')
, DECODE(BITAND(DECODE(BITAND(TP.FLAGS
, 65536)
, 65536
, DS.BFP_STG
, S.CACHEHINT)
, 12)/4
, 1
, 'KEEP'
, 2
, 'NONE'
, 'DEFAULT')
, DECODE(BITAND(DECODE(BITAND(TP.FLAGS
, 65536)
, 65536
, DS.BFP_STG
, S.CACHEHINT)
, 48)/16
, 1
, 'KEEP'
, 2
, 'NONE'
, 'DEFAULT')
, DECODE(BITAND(TP.FLAGS
, 16)
, 0
, 'NO'
, 'YES')
, DECODE(BITAND(TP.FLAGS
, 8)
, 0
, 'NO'
, 'YES')
, DECODE(BITAND(O.FLAGS
, 16384)
, 0
, 'NO'
, 'YES')
, CASE BITAND(O.FLAGS
, 16384) --IS THE OBJECT A NESTED TABLE PARTITION? WHEN 16384 THEN (SELECT O1.SUBNAME
FROM OBJ$ O1
WHERE O1.OBJ#= (SELECT TP1.OBJ#
FROM TABPARTV$ TP1
, TABPARTV$ TP2
, NTAB$ NT
WHERE TP2.BO# = TP.BO#
AND TP2.OBJ# = TP.OBJ#
AND TP1.PART# = TP2.PART#
AND TP1.BO#=NT.OBJ#
AND NT.NTAB#=TP.BO#)) ELSE NULL END
, DECODE(BITAND(TP.FLAGS
, 32768)
, 32768
, 'YES'
, 'NO')
, DECODE(BITAND(TP.FLAGS
, 65536)
, 65536
, 'NO'
, 'YES')
, DECODE(BITAND(TP.FLAGS
, 2097152)
, 2097152
, 'OFF'
, 'ON')
, --INMEMORY CASE WHEN (BITAND(TP.FLAGS
, 65536) = 65536) THEN -- FLAGS/IMCFLAG_STG (STGDEF.H DECODE(BITAND(DS.FLAGS_STG
, 6291456)
, 2097152
, 'ENABLED'
, 4194304
, 'DISABLED'
, 'DISABLED') ELSE -- KTSSCFLG (KTSCTS.H) DECODE(BITAND(S.SPARE1
, 70373039144960)
, 4294967296
, 'ENABLED'
, 70368744177664
, 'DISABLED'
, 'DISABLED') END
, -- INMEMORY_PRIORITY CASE WHEN (BITAND(TP.FLAGS
, 65536) = 65536) THEN DECODE(BITAND(DS.FLAGS_STG
, 2097152)
, 2097152
, DECODE(BITAND(DS.IMCFLAG_STG
, 4)
, 4
, DECODE(BITAND(DS.FLAGS_STG
, 2097152)
, 2097152
, DECODE(BITAND(DS.IMCFLAG_STG
, 7936)
, 256
, 'NONE'
, 512
, 'LOW'
, 1024
, 'MEDIUM'
, 2048
, 'HIGH'
, 4096
, 'CRITICAL'
, 'UNKNOWN')
, NULL)
, 'NONE')
, NULL) ELSE DECODE(BITAND(S.SPARE1
, 4294967296)
, 4294967296
, DECODE(BITAND(S.SPARE1
, 34359738368)
, 34359738368
, DECODE(BITAND(S.SPARE1
, 61572651155456)
, 8796093022208
, 'LOW'
, 17592186044416
, 'MEDIUM'
, 35184372088832
, 'HIGH'
, 52776558133248
, 'CRITICAL'
, 'NONE')
, 'NONE')
, NULL) END
, -- INMEMORY_DISTRIBUTE CASE WHEN (BITAND(TP.FLAGS
, 65536) = 65536) THEN DECODE(BITAND(DS.FLAGS_STG
, 2097152)
, 2097152
, DECODE(BITAND(DS.IMCFLAG_STG
, 1)
, 1
, DECODE(BITAND(DS.IMCFLAG_STG
, (16+32))
, 16
, 'BY ROWID RANGE'
, 32
, 'BY PARTITION'
, 48
, 'BY SUBPARTITION'
, 0
, 'AUTO')
, NULL)
, NULL) ELSE DECODE(BITAND(S.SPARE1
, 4294967296)
, 4294967296
, DECODE(BITAND(S.SPARE1
, 8589934592)
, 8589934592
, DECODE(BITAND(S.SPARE1
, 206158430208)
, 68719476736
, 'BY ROWID RANGE'
, 137438953472
, 'BY PARTITION'
, 206158430208
, 'BY SUBPARTITION'
, 0
, 'AUTO')
, NULL)
, NULL) END
, -- INMEMORY_COMPRESSION CASE WHEN (BITAND(TP.FLAGS
, 65536) = 65536) THEN DECODE(BITAND(DS.FLAGS_STG
, 2097152)
, 2097152
, DECODE(BITAND(DS.IMCFLAG_STG
, (2+8+64+128))
, 2
, 'NO MEMCOMPRESS'
, 8
, 'FOR DML'
, 10
, 'FOR QUERY LOW'
, 64
, 'FOR QUERY HIGH'
, 66
, 'FOR CAPACITY LOW'
, 72
, 'FOR CAPACITY HIGH'
, 'UNKNOWN')
, NULL) ELSE DECODE(BITAND(S.SPARE1
, 4294967296)
, 4294967296
, DECODE(BITAND(S.SPARE1
, 841813590016)
, 17179869184
, 'NO MEMCOMPRESS'
, 274877906944
, 'FOR DML'
, 292057776128
, 'FOR QUERY LOW'
, 549755813888
, 'FOR QUERY HIGH'
, 566935683072
, 'FOR CAPACITY LOW'
, 824633720832
, 'FOR CAPACITY HIGH'
, 'UNKNOWN')
, NULL) END
, -- INMEMORY_DUPLICATE CASE WHEN (BITAND(TP.FLAGS
, 65536) = 65536) THEN DECODE(BITAND(DS.FLAGS_STG
, 2097152)
, 2097152
, DECODE(BITAND(DS.IMCFLAG_STG
, (8192+16384))
, 8192
, 'NO DUPLICATE'
, 16384
, 'DUPLICATE'
, 24576
, 'DUPLICATE ALL'
, NULL)
, NULL) ELSE DECODE(BITAND(S.SPARE1
, 4294967296)
, 4294967296
, DECODE(BITAND(S.SPARE1
, 6597069766656)
, 2199023255552
, 'NO DUPLICATE'
, 4398046511104
, 'DUPLICATE'
, 6597069766656
, 'DUPLICATE ALL'
, NULL)
, NULL) END FROM OBJ$ O
, TABPART$ TP
, TS$ TS
, SYS.SEG$ S
, USER$ U
, TAB$ T
, SYS.DEFERRED_STG$ DS WHERE O.OBJ# = TP.OBJ#
AND TS.TS# = TP.TS#
AND U.USER# = O.OWNER# AND TP.OBJ# = DS.OBJ#(+) AND TP.FILE#=S.FILE#(+)
AND TP.BLOCK#=S.BLOCK#(+)
AND TP.TS#=S.TS#(+) AND BITAND(T.PROPERTY
, 64) != 64 AND BITAND(TP.FLAGS
, 8388608) = 0
AND /* FILTER OUT HIDDEN PARTITIONS */ TP.BO# = T.OBJ#
AND BITAND(T.TRIGFLAG
, 1073741824) != 1073741824
AND O.NAMESPACE = 1
AND O.REMOTEOWNER IS NULL
AND O.LINKNAME IS NULL UNION ALL -- IOT PARTITIONS SELECT U.NAME
, O.NAME
, 'NO'
, O.SUBNAME
, 0
, TP.HIBOUNDVAL
, TP.HIBOUNDLEN
, ROW_NUMBER() OVER (PARTITION BY U.NAME
, O.NAME ORDER BY TP.PART#)
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, 'N/A'
, 'N/A'
, TP.ROWCNT
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, 0
, TP.CHNCNT
, TP.AVGRLN
, TP.SAMPLESIZE
, TP.ANALYZETIME
, NULL
, NULL
, NULL
, DECODE(BITAND(TP.FLAGS
, 16)
, 0
, 'NO'
, 'YES')
, DECODE(BITAND(TP.FLAGS
, 8)
, 0
, 'NO'
, 'YES')
, 'N/A'
, 'N/A'
, 'N/A'
, DECODE(BITAND(TP.FLAGS
, 65536)
, 65536
, 'NO'
, 'YES')
, DECODE(BITAND(TP.FLAGS
, 2097152)
, 2097152
, 'OFF'
, 'ON')
, 'N/A'
, 'N/A'
, 'N/A'
, 'N/A'
, 'N/A' FROM OBJ$ O
, TABPART$ TP
, USER$ U
, TAB$ T WHERE O.OBJ# = TP.OBJ#
AND O.OWNER# = U.USER# AND TP.BO# = T.OBJ#
AND BITAND(T.PROPERTY
, 64) = 64 AND BITAND(T.TRIGFLAG
, 1073741824) != 1073741824
AND O.NAMESPACE = 1
AND O.REMOTEOWNER IS NULL
AND O.LINKNAME IS NULL UNION ALL -- COMPOSITE PARTITIONS SELECT U.NAME
, O.NAME
, 'YES'
, O.SUBNAME
, TCP.SUBPARTCNT
, TCP.HIBOUNDVAL
, TCP.HIBOUNDLEN
, ROW_NUMBER() OVER (PARTITION BY U.NAME
, O.NAME ORDER BY TCP.PART#)
, TS.NAME
, TCP.DEFPCTFREE
, DECODE(BITAND(TS.FLAGS
, 32)
, 32
, TO_NUMBER(NULL)
, TCP.DEFPCTUSED)
, TCP.DEFINITRANS
, TCP.DEFMAXTRANS
, TCP.DEFINIEXTS
, TCP.DEFEXTSIZE
, TCP.DEFMINEXTS
, TCP.DEFMAXEXTS
, TCP.DEFMAXSIZE
, TCP.DEFEXTPCT
, DECODE(BITAND(TS.FLAGS
, 32)
, 32
, TO_NUMBER(NULL)
, TCP.DEFLISTS)
, DECODE(BITAND(TS.FLAGS
, 32)
, 32
, TO_NUMBER(NULL)
, TCP.DEFGROUPS)
, DECODE(TCP.DEFLOGGING
, 0
, 'NONE'
, 1
, 'YES'
, 2
, 'NO'
, 'UNKNOWN')
, DECODE(BITAND(TCP.SPARE2
, 3)
, 1
, 'ENABLED'
, 2
, 'DISABLED'
, 'NONE')
, DECODE(BITAND(TCP.SPARE2
, 1)
, 0
, NULL
, CASE BITAND(TCP.SPARE2
, 63) -- 1ST 6 BITS USED WHEN 1 THEN 'BASIC' -- 00000001 WHEN 5 THEN 'ADVANCED' -- 00000101 WHEN 9 THEN 'QUERY LOW' -- 00001001 WHEN 17 THEN 'QUERY HIGH' -- 00010001 WHEN 25 THEN 'ARCHIVE LOW' -- 00011001 WHEN 33 THEN 'ARCHIVE HIGH' -- 00100001 WHEN 73 THEN 'QUERY LOW ROW LEVEL LOCKING' -- 01001001 WHEN 81 THEN 'QUERY HIGH ROW LEVEL LOCKING' -- 01010001 WHEN 89 THEN 'ARCHIVE LOW ROW LEVEL LOCKING' -- 01011001 WHEN 97 THEN 'ARCHIVE HIGH ROW LEVEL LOCKING' -- 01100001 ELSE 'UNKNOWN' END)
, -- INTERNAL ILEVELS TCP.ROWCNT
, TCP.BLKCNT
, TCP.EMPCNT
, TCP.AVGSPC
, TCP.CHNCNT
, TCP.AVGRLN
, TCP.SAMPLESIZE
, TCP.ANALYZETIME
, DECODE(BITAND(TCP.DEFBUFPOOL
, 3)
, 1
, 'KEEP'
, 2
, 'RECYCLE'
, 'DEFAULT')
, DECODE(BITAND(TCP.DEFBUFPOOL
, 12)/4
, 1
, 'KEEP'
, 2
, 'NONE'
, 'DEFAULT')
, DECODE(BITAND(TCP.DEFBUFPOOL
, 48)/16
, 1
, 'KEEP'
, 2
, 'NONE'
, 'DEFAULT')
, DECODE(BITAND(TCP.FLAGS
, 16)
, 0
, 'NO'
, 'YES')
, DECODE(BITAND(TCP.FLAGS
, 8)
, 0
, 'NO'
, 'YES')
, 'N/A'
, 'N/A'
, DECODE(BITAND(TCP.FLAGS
, 32768)
, 32768
, 'YES'
, 'NO')
, DECODE(BITAND(TCP.SPARE2
, 768)
, 256
, 'NO'
, 512
, 'YES'
, 'NONE')
, DECODE(BITAND(TCP.SPARE2
, 3072)
, 1024
, 'ON'
, 2048
, 'OFF'
, 'NONE')
, -- INMEMORY -- DEFIMCFLAGS_KKPACPCD DECODE(BITAND(TCP.SPARE2
, 196608)
, 65536
, 'ENABLED'
, 131072
, 'DISABLED'
, 'DISABLED')
, -- INMEMORY_PRIORITY DECODE(BITAND(TCP.SPARE2
, 65536)
, 65536
, DECODE(BITAND(TCP.SPARE2
, 1048576)
, 1048576
, DECODE(BITAND(TCP.SPARE2
, 1879048192)/268435456
, 0
, 'NONE'
, 1
, 'LOW'
, 2
, 'MEDIUM'
, 3
, 'HIGH'
, 4
, 'CRITICAL'
, 'UNKNOWN')
, 'NONE')
, NULL)
, -- INMEMORY_DISTRIBUTE DECODE(BITAND(TCP.SPARE2
, 65536)
, 65536
, DECODE(BITAND(TCP.SPARE2
, 262144)
, 262144
, DECODE(BITAND(TCP.SPARE2
, (2097152+4194304))
, 0
, 'AUTO'
, 2097152
, 'BY ROWID RANGE'
, 4194304
, 'BY PARTITION'
, 6291456
, 'BY SUBPARTITION')
, NULL)
, NULL)
, -- INMEMORY_COMPRESSION DECODE(BITAND(TCP.SPARE2
, 65536)
, 65536
, DECODE(BITAND(TCP.SPARE2
, (524288+8388608+16777216))
, 524288
, 'NO MEMCOMPRESS'
, 8388608
, 'FOR DML'
, (524288+8388608)
, 'FOR QUERY LOW'
, 16777216
, 'FOR QUERY HIGH'
, (524288+16777216)
, 'FOR CAPACITY LOW'
, (8388608+16777216)
, 'FOR CAPACITY HIGH'
, NULL)
, NULL)
, -- INMEMORY_DUPLICATE DECODE (BITAND(TCP.SPARE2
, 65536)
, 65536
, DECODE(BITAND(TCP.SPARE2
, (67108864+134217728))
, 67108864
, 'NO DUPLICATE'
, 134217728
, 'DUPLICATE'
, (67108864+134217728)
, 'DUPLICATE ALL'
, NULL)
, NULL) FROM OBJ$ O
, TABCOMPART$ TCP
, TS$ TS
, USER$ U
, TAB$ T WHERE O.OBJ# = TCP.OBJ#
AND TCP.DEFTS# = TS.TS#
AND U.USER# = O.OWNER# AND TCP.BO# = T.OBJ#
AND BITAND(T.TRIGFLAG
, 1073741824) != 1073741824
AND BITAND(T.PROPERTY
, 64) != 64
AND BITAND(TCP.FLAGS
, 8388608) = 0 /* FILTER OUT HIDDEN PARTITIONS */
AND O.NAMESPACE = 1
AND O.REMOTEOWNER IS NULL
AND O.LINKNAME IS NULL