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',
'UNKNOWN'),
null)
else
decode(bitand(s.spare1, 4294967296), 4294967296,
decode(bitand(s.spare1, 6597069766656),
2199023255552, 'NO DUPLICATE',
4398046511104, 'DUPLICATE',
6597069766656, 'DUPLICATE ALL', 'UNKNOWN'),
null)
end
from obj$ o, tabpart$ tp, ts$ ts, sys.seg$ s, user$ u, tab$ t,
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
tp.bo# = t.obj# and bitand(t.trigflag, 1073741824) != 1073741824 and
o.namespace = 1 and o.remoteowner IS NULL and o.linkname IS NULL and
bitand(tp.flags, 8388608) = 0 and /* filter out hidden partitions */
(o.owner# = userenv('SCHEMAID')
or tp.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 */)
)
)
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.trigflag, 1073741824) != 1073741824 and
bitand(t.property, 64) = 64 and
o.namespace = 1 and o.remoteowner IS NULL and o.linkname IS NULL and
(o.owner# = userenv('SCHEMAID')
or tp.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 */)
)
)
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
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 and /* filter out hidden partitions */
o.namespace = 1 and o.remoteowner IS NULL and o.linkname IS NULL and
(o.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 */)
)
)
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'
,
'UNKNOWN')
,
NULL)
ELSE
DECODE(BITAND(S.SPARE1
, 4294967296)
, 4294967296
,
DECODE(BITAND(S.SPARE1
, 6597069766656)
,
2199023255552
, 'NO DUPLICATE'
,
4398046511104
, 'DUPLICATE'
,
6597069766656
, 'DUPLICATE ALL'
, 'UNKNOWN')
,
NULL)
END
FROM OBJ$ O
, TABPART$ TP
, TS$ TS
, SYS.SEG$ S
, USER$ U
, TAB$ T
,
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
TP.BO# = T.OBJ#
AND BITAND(T.TRIGFLAG
, 1073741824) != 1073741824 AND
O.NAMESPACE = 1
AND O.REMOTEOWNER IS NULL
AND O.LINKNAME IS NULL AND
BITAND(TP.FLAGS
, 8388608) = 0
AND /* FILTER OUT HIDDEN PARTITIONS */
(O.OWNER# = USERENV('SCHEMAID')
OR TP.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 */)
)
)
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.TRIGFLAG
, 1073741824) != 1073741824 AND
BITAND(T.PROPERTY
, 64) = 64 AND
O.NAMESPACE = 1
AND O.REMOTEOWNER IS NULL
AND O.LINKNAME IS NULL AND
(O.OWNER# = USERENV('SCHEMAID')
OR TP.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 */)
)
)
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
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
AND /* FILTER OUT HIDDEN PARTITIONS */
O.NAMESPACE = 1
AND O.REMOTEOWNER IS NULL
AND O.LINKNAME IS NULL AND
(O.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 */)
)
)
|
|
|