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 sys.obj$ so, sys.obj$ po, tabcompart$ tcp, sys.tabsubpart$ tsp,
sys.tab$ t, sys.ts$ ts, sys.seg$ s, sys.user$ u, sys.deferred_stg$ ds
where so.obj# = tsp.obj# and po.obj# = tsp.pobj# and tcp.obj# = tsp.pobj# and
tcp.bo# = t.obj# and bitand(t.trigflag, 1073741824) != 1073741824 and
tsp.ts# = ts.ts# and u.user# = po.owner# and tsp.obj# = ds.obj#(+) and
tsp.file# = s.file#(+) and tsp.block# = s.block#(+) and
bitand(tcp.flags, 8388608) = 0 and /* filter out hidden partitions */
bitand(tsp.flags, 8388608) = 0 and /* filter out hidden partitions */
tsp.ts# = s.ts#(+)
and 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
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 SYS.OBJ$ SO
, SYS.OBJ$ PO
, TABCOMPART$ TCP
, SYS.TABSUBPART$ TSP
,
SYS.TAB$ T
, SYS.TS$ TS
, SYS.SEG$ S
, SYS.USER$ U
, SYS.DEFERRED_STG$ DS
WHERE SO.OBJ# = TSP.OBJ#
AND PO.OBJ# = TSP.POBJ#
AND TCP.OBJ# = TSP.POBJ# AND
TCP.BO# = T.OBJ#
AND BITAND(T.TRIGFLAG
, 1073741824) != 1073741824 AND
TSP.TS# = TS.TS#
AND U.USER# = PO.OWNER#
AND TSP.OBJ# = DS.OBJ#(+) AND
TSP.FILE# = S.FILE#(+)
AND TSP.BLOCK# = S.BLOCK#(+) AND
BITAND(TCP.FLAGS
, 8388608) = 0
AND /* FILTER OUT HIDDEN PARTITIONS */
BITAND(TSP.FLAGS
, 8388608) = 0
AND /* FILTER OUT HIDDEN PARTITIONS */
TSP.TS# = S.TS#(+)
AND 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
|
|
|