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.

[Home] [Help]

VIEW: SYS.ALL_TAB_SUBPARTITIONS

Source

View Text - Preformatted


select u.name, po.name, po.subname, so.subname,
       tsp.hiboundval, tsp.hiboundlen,
       row_number() over (partition by u.name, po.name, po.subname
                          order by tsp.subpart#),
       ts.name, tsp.pctfree$,
       decode(bitand(ts.flags, 32), 32, to_number(NULL), tsp.pctused$),
       tsp.initrans, tsp.maxtrans,
       decode(bitand(tsp.flags, 65536), 65536,
              ds.initial_stg * ts.blocksize, s.iniexts * ts.blocksize),
       decode(bitand(tsp.flags, 65536), 65536,
              ds.next_stg * ts.blocksize, s.extsize * ts.blocksize),
       decode(bitand(tsp.flags, 65536), 65536, ds.minext_stg, s.minexts),
       decode(bitand(tsp.flags, 65536), 65536, ds.maxext_stg, s.maxexts),
       decode(bitand(tsp.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(tsp.flags, 65536), 65536, ds.pctinc_stg, s.extpct)),
       decode(bitand(ts.flags, 32), 32, to_number(NULL),
              decode(bitand(tsp.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(tsp.flags, 65536), 65536,
                     decode(ds.maxins_stg, 0, 1, ds.maxins_stg),
                     decode(s.groups, 0, 1, s.groups))),
       decode(mod(trunc(tsp.flags / 4), 2), 0, 'YES', 'NO'),
       case when (bitand(tsp.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(tsp.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,
       tsp.rowcnt, tsp.blkcnt, tsp.empcnt, tsp.avgspc, tsp.chncnt,
       tsp.avgrln, tsp.samplesize, tsp.analyzetime,
       decode(bitand(decode(bitand(tsp.flags, 65536), 65536, ds.bfp_stg, s.cachehint), 3),
              1, 'KEEP', 2, 'RECYCLE', 'DEFAULT'),
       decode(bitand(decode(bitand(tsp.flags, 65536), 65536, ds.bfp_stg, s.cachehint), 12)/4,
              1, 'KEEP', 2, 'NONE', 'DEFAULT'),
       decode(bitand(decode(bitand(tsp.flags, 65536), 65536, ds.bfp_stg, s.cachehint), 48)/16,
              1, 'KEEP', 2, 'NONE', 'DEFAULT'),
       decode(bitand(tsp.flags, 16), 0, 'NO', 'YES'),
       decode(bitand(tsp.flags, 8), 0, 'NO', 'YES'),
       decode(bitand(tsp.flags, 32768), 32768, 'YES', 'NO'),
       decode(bitand(tsp.flags, 65536), 65536, 'NO', 'YES'),
       decode(bitand(tsp.flags, 2097152), 2097152, 'OFF', 'ON'),
       --INMEMORY
       case when (bitand(tsp.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(tsp.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(tsp.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(tsp.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(tsp.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$ po, obj$ so, tabcompart$ tcp, tabsubpart$ tsp, tab$ t,
       ts$ ts, sys.seg$ s, user$ u, sys.deferred_stg$ ds
where  so.obj# = tsp.obj# and po.obj# = tcp.obj# and tcp.obj# = tsp.pobj# and
       tcp.bo# = t.obj# and bitand(t.trigflag, 1073741824) != 1073741824 and
       ts.ts# = tsp.ts# and u.user# = po.owner# and tsp.file# = s.file#(+) and
       tsp.block# = s.block#(+) and tsp.ts# = s.ts#(+) and
       tsp.obj# = ds.obj#(+) and
       bitand(tcp.flags, 8388608) = 0 and   /* filter out hidden partitions */
       bitand(tsp.flags, 8388608) = 0 and   /* filter out hidden partitions */
       po.namespace = 1 and po.remoteowner IS NULL and po.linkname IS NULL and
       so.namespace = 1 and so.remoteowner IS NULL and so.linkname IS NULL and
       ((po.owner# = userenv('SCHEMAID') and so.owner# = userenv('SCHEMAID'))
        or tcp.bo# in
            (select oa.obj#
             from sys.objauth$ oa
             where grantee# in ( select kzsrorol
                                 from x$kzsro
                               )
            )
        or /* user has system privileges */
         exists (select null from v$enabledprivs
                 where priv_number in (-45 /* LOCK ANY TABLE */,
                                       -47 /* SELECT ANY TABLE */,
                                       -397/* READ ANY TABLE */,
                                       -48 /* INSERT ANY TABLE */,
                                       -49 /* UPDATE ANY TABLE */,
                                       -50 /* DELETE ANY TABLE */)
                 )
      )

View Text - HTML Formatted

SELECT U.NAME
, PO.NAME
, PO.SUBNAME
, SO.SUBNAME
, TSP.HIBOUNDVAL
, TSP.HIBOUNDLEN
, ROW_NUMBER() OVER (PARTITION BY U.NAME
, PO.NAME
, PO.SUBNAME ORDER BY TSP.SUBPART#)
, TS.NAME
, TSP.PCTFREE$
, DECODE(BITAND(TS.FLAGS
, 32)
, 32
, TO_NUMBER(NULL)
, TSP.PCTUSED$)
, TSP.INITRANS
, TSP.MAXTRANS
, DECODE(BITAND(TSP.FLAGS
, 65536)
, 65536
, DS.INITIAL_STG * TS.BLOCKSIZE
, S.INIEXTS * TS.BLOCKSIZE)
, DECODE(BITAND(TSP.FLAGS
, 65536)
, 65536
, DS.NEXT_STG * TS.BLOCKSIZE
, S.EXTSIZE * TS.BLOCKSIZE)
, DECODE(BITAND(TSP.FLAGS
, 65536)
, 65536
, DS.MINEXT_STG
, S.MINEXTS)
, DECODE(BITAND(TSP.FLAGS
, 65536)
, 65536
, DS.MAXEXT_STG
, S.MAXEXTS)
, DECODE(BITAND(TSP.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(TSP.FLAGS
, 65536)
, 65536
, DS.PCTINC_STG
, S.EXTPCT))
, DECODE(BITAND(TS.FLAGS
, 32)
, 32
, TO_NUMBER(NULL)
, DECODE(BITAND(TSP.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(TSP.FLAGS
, 65536)
, 65536
, DECODE(DS.MAXINS_STG
, 0
, 1
, DS.MAXINS_STG)
, DECODE(S.GROUPS
, 0
, 1
, S.GROUPS)))
, DECODE(MOD(TRUNC(TSP.FLAGS / 4)
, 2)
, 0
, 'YES'
, 'NO')
, CASE WHEN (BITAND(TSP.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(TSP.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
, TSP.ROWCNT
, TSP.BLKCNT
, TSP.EMPCNT
, TSP.AVGSPC
, TSP.CHNCNT
, TSP.AVGRLN
, TSP.SAMPLESIZE
, TSP.ANALYZETIME
, DECODE(BITAND(DECODE(BITAND(TSP.FLAGS
, 65536)
, 65536
, DS.BFP_STG
, S.CACHEHINT)
, 3)
, 1
, 'KEEP'
, 2
, 'RECYCLE'
, 'DEFAULT')
, DECODE(BITAND(DECODE(BITAND(TSP.FLAGS
, 65536)
, 65536
, DS.BFP_STG
, S.CACHEHINT)
, 12)/4
, 1
, 'KEEP'
, 2
, 'NONE'
, 'DEFAULT')
, DECODE(BITAND(DECODE(BITAND(TSP.FLAGS
, 65536)
, 65536
, DS.BFP_STG
, S.CACHEHINT)
, 48)/16
, 1
, 'KEEP'
, 2
, 'NONE'
, 'DEFAULT')
, DECODE(BITAND(TSP.FLAGS
, 16)
, 0
, 'NO'
, 'YES')
, DECODE(BITAND(TSP.FLAGS
, 8)
, 0
, 'NO'
, 'YES')
, DECODE(BITAND(TSP.FLAGS
, 32768)
, 32768
, 'YES'
, 'NO')
, DECODE(BITAND(TSP.FLAGS
, 65536)
, 65536
, 'NO'
, 'YES')
, DECODE(BITAND(TSP.FLAGS
, 2097152)
, 2097152
, 'OFF'
, 'ON')
, --INMEMORY CASE WHEN (BITAND(TSP.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(TSP.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(TSP.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(TSP.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(TSP.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$ PO
, OBJ$ SO
, TABCOMPART$ TCP
, TABSUBPART$ TSP
, TAB$ T
, TS$ TS
, SYS.SEG$ S
, USER$ U
, SYS.DEFERRED_STG$ DS WHERE SO.OBJ# = TSP.OBJ#
AND PO.OBJ# = TCP.OBJ#
AND TCP.OBJ# = TSP.POBJ# AND TCP.BO# = T.OBJ#
AND BITAND(T.TRIGFLAG
, 1073741824) != 1073741824 AND TS.TS# = TSP.TS#
AND U.USER# = PO.OWNER#
AND TSP.FILE# = S.FILE#(+) AND TSP.BLOCK# = S.BLOCK#(+)
AND TSP.TS# = S.TS#(+) AND TSP.OBJ# = DS.OBJ#(+) AND BITAND(TCP.FLAGS
, 8388608) = 0
AND /* FILTER OUT HIDDEN PARTITIONS */ BITAND(TSP.FLAGS
, 8388608) = 0
AND /* FILTER OUT HIDDEN PARTITIONS */ PO.NAMESPACE = 1
AND PO.REMOTEOWNER IS NULL
AND PO.LINKNAME IS NULL AND SO.NAMESPACE = 1
AND SO.REMOTEOWNER IS NULL
AND SO.LINKNAME IS NULL AND ((PO.OWNER# = USERENV('SCHEMAID')
AND SO.OWNER# = USERENV('SCHEMAID')) OR TCP.BO# IN (SELECT OA.OBJ#
FROM SYS.OBJAUTH$ OA
WHERE GRANTEE# IN ( SELECT KZSROROL
FROM X$KZSRO ) ) OR /* USER HAS SYSTEM PRIVILEGES */ EXISTS (SELECT NULL
FROM V$ENABLEDPRIVS
WHERE PRIV_NUMBER IN (-45 /* LOCK ANY TABLE */
, -47 /* SELECT ANY TABLE */
, -397/* READ ANY TABLE */
, -48 /* INSERT ANY TABLE */
, -49 /* UPDATE ANY TABLE */
, -50 /* DELETE ANY TABLE */) ) )