select u.name, io.name, 'NO', io.subname, 0,
ip.hiboundval, ip.hiboundlen,
row_number() over (partition by u.name, io.name order by ip.part#),
decode(bitand(ip.flags, 1), 1, 'UNUSABLE', 'USABLE'),ts.name,
ip.pctfree$, ip.initrans, ip.maxtrans,
decode(bitand(ip.flags, 65536), 65536,
ds.initial_stg * ts.blocksize, s.iniexts * ts.blocksize),
decode(bitand(ip.flags, 65536), 65536,
ds.next_stg * ts.blocksize, s.extsize * ts.blocksize),
decode(bitand(ip.flags, 65536), 65536, ds.minext_stg, s.minexts),
decode(bitand(ip.flags, 65536), 65536, ds.maxext_stg, s.maxexts),
decode(bitand(ip.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(ip.flags, 65536), 65536, ds.pctinc_stg, s.extpct)),
decode(bitand(ts.flags, 32), 32, to_number(NULL),
decode(bitand(ip.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(ip.flags, 65536), 65536,
decode(ds.maxins_stg, 0, 1, ds.maxins_stg),
decode(s.groups, 0, 1, s.groups))),
decode(mod(trunc(ip.flags / 4), 2), 0, 'YES', 'NO'),
case
when bitand(ip.flags, 1024) = 1024 then 'ENABLED'
else
case when (bitand(ip.flags, 65536) = 65536) then
decode(bitand(ds.flags_stg, 4), 4,
decode(bitand(ds.cmpflag_stg, 6),
4, 'ADVANCED LOW',
2, 'ADVANCED HIGH',
NULL),
'DISABLED')
else
decode(bitand(s.spare1, 2048), 2048,
decode(bitand(s.spare1, 16777216 + 1048576),
16777216, 'ADVANCED HIGH',
1048576, 'ADVANCED LOW',
NULL),
'DISABLED')
end
end,
ip.blevel, ip.leafcnt, ip.distkey, ip.lblkkey, ip.dblkkey,
ip.clufac, ip.rowcnt, ip.samplesize, ip.analyzetime,
decode(bitand(decode(bitand(ip.flags, 65536), 65536, ds.bfp_stg, s.cachehint), 3),
1, 'KEEP', 2, 'RECYCLE', 'DEFAULT'),
decode(bitand(decode(bitand(ip.flags, 65536), 65536, ds.bfp_stg, s.cachehint), 12)/4,
1, 'KEEP', 2, 'NONE', 'DEFAULT'),
decode(bitand(decode(bitand(ip.flags, 65536), 65536, ds.bfp_stg, s.cachehint), 48)/16,
1, 'KEEP', 2, 'NONE', 'DEFAULT'),
decode(bitand(ip.flags, 8), 0, 'NO', 'YES'), ip.pctthres$,
decode(bitand(ip.flags, 16), 0, 'NO', 'YES'), '','',
decode(bitand(ip.flags, 32768), 32768, 'YES', 'NO'),
decode(bitand(ip.flags, 65536), 65536, 'NO', 'YES'),
decode(bitand(ip.flags, 262144), 262144, 'YES', 'NO')
from obj$ io, indpart$ ip, ts$ ts, sys.seg$ s, ind$ i, sys.user$ u, tab$ t,
sys.deferred_stg$ ds
where io.obj# = ip.obj# and ts.ts# = ip.ts# and ip.file#=s.file#(+) and
ip.block#=s.block#(+) and ip.ts#=s.ts#(+) and io.owner# = u.user# and
i.obj# = ip.bo# and i.bo# = t.obj# and ip.obj# = ds.obj#(+) and
bitand(ip.flags, 8388608) = 0 and /* filter out hidden partitions */
bitand(t.trigflag, 1073741824) != 1073741824 and
i.type# != 8 and /* not LOB index */
i.type# != 9 and /* not DOMAIN index */
io.namespace = 4 and io.remoteowner IS NULL and io.linkname IS NULL and
(io.owner# = userenv('SCHEMAID')
or
i.bo# in (select obj#
from objauth$
where grantee# in ( select kzsrorol
from x$kzsro
)
)
or
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
select u.name, io.name, 'YES', io.subname, icp.subpartcnt,
icp.hiboundval, icp.hiboundlen,
row_number() over (partition by u.name, io.name order by icp.part#) ,
'N/A', ts.name,
icp.defpctfree, icp.definitrans, icp.defmaxtrans,
icp.definiexts, icp.defextsize, icp.defminexts, icp.defmaxexts,
icp.defmaxsize, icp.defextpct, icp.deflists, icp.defgroups,
decode(icp.deflogging, 0, 'NONE', 1, 'YES', 2, 'NO', 'UNKNOWN'),
case
when bitand(icp.spare3, 8) = 8 then 'ADVANCED LOW'
when bitand(icp.spare3, 4) = 4 then 'ADVANCED HIGH'
else
decode(bitand(icp.flags, 1024), 1024, 'ENABLED', 'DISABLED')
end,
icp.blevel, icp.leafcnt, icp.distkey, icp.lblkkey, icp.dblkkey,
icp.clufac, icp.rowcnt, icp.samplesize, icp.analyzetime,
decode(bitand(icp.defbufpool, 3), 1, 'KEEP', 2, 'RECYCLE', 'DEFAULT'),
decode(bitand(icp.defbufpool, 12)/4, 1, 'KEEP', 2, 'NONE', 'DEFAULT'),
decode(bitand(icp.defbufpool, 48)/16, 1, 'KEEP', 2, 'NONE', 'DEFAULT'),
decode(bitand(icp.flags, 8), 0, 'NO', 'YES'), TO_NUMBER(NULL),
decode(bitand(icp.flags, 16), 0, 'NO', 'YES'), '','',
decode(bitand(icp.flags, 32768), 32768, 'YES', 'NO'), 'N/A', 'N/A'
from obj$ io, indcompart$ icp, ts$ ts, ind$ i, user$ u, tab$ t
where io.obj# = icp.obj# and icp.defts# = ts.ts# (+) and io.owner# = u.user# and
i.obj# = icp.bo# and i.bo# = t.obj# and
bitand(t.trigflag, 1073741824) != 1073741824 and
bitand(icp.flags, 8388608) = 0 and /* filter out hidden partitions */
i.type# != 8 and /* not LOB index */
i.type# != 9 and /* not DOMAIN index */
io.namespace = 4 and io.remoteowner IS NULL and io.linkname IS NULL and
(io.owner# = userenv('SCHEMAID')
or
i.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
select u.name, io.name, 'NO', io.subname, 0,
ip.hiboundval, ip.hiboundlen,
row_number() over (partition by u.name, io.name order by ip.part#),
decode(bitand(ip.flags, 1), 1, 'UNUSABLE',
decode(bitand(ip.flags, 4096), 4096, 'INPROGRS', 'USABLE')),
null, ip.pctfree$, ip.initrans, ip.maxtrans,
0, 0, 0, 0, 0, 0, 0, 0,
decode(mod(trunc(ip.flags / 4), 2), 0, 'YES', 'NO'),
'N/A',
ip.blevel, ip.leafcnt, ip.distkey, ip.lblkkey, ip.dblkkey,
ip.clufac, ip.rowcnt, ip.samplesize, ip.analyzetime,
'DEFAULT', 'DEFAULT', 'DEFAULT',
decode(bitand(ip.flags, 8), 0, 'NO', 'YES'), ip.pctthres$,
decode(bitand(ip.flags, 16), 0, 'NO', 'YES'),
decode(i.type#,
9, decode(bitand(ip.flags, 8192), 8192, 'FAILED', 'VALID'),
''),
ipp.parameters,
decode(bitand(ip.flags, 32768), 32768, 'YES', 'NO'),
decode(bitand(ip.flags, 65536), 65536, 'NO', 'YES'),
decode(bitand(ip.flags, 262144), 262144, 'YES', 'NO')
from obj$ io, indpart$ ip, ind$ i, sys.user$ u, indpart_param$ ipp, tab$ t
where io.obj# = ip.obj# and io.owner# = u.user# and
i.obj# = ip.bo# and ip.obj# = ipp.obj# and
i.bo# = t.obj# and bitand(t.trigflag, 1073741824) != 1073741824 and
io.namespace = 4 and io.remoteowner IS NULL and io.linkname IS NULL and
(io.owner# = userenv('SCHEMAID')
or
i.bo# in (select obj#
from objauth$
where grantee# in ( select kzsrorol
from x$kzsro
)
)
or
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
, IO.NAME
, 'NO'
, IO.SUBNAME
, 0
,
IP.HIBOUNDVAL
, IP.HIBOUNDLEN
,
ROW_NUMBER() OVER (PARTITION BY U.NAME
, IO.NAME ORDER BY IP.PART#)
,
DECODE(BITAND(IP.FLAGS
, 1)
, 1
, 'UNUSABLE'
, 'USABLE')
, TS.NAME
,
IP.PCTFREE$
, IP.INITRANS
, IP.MAXTRANS
,
DECODE(BITAND(IP.FLAGS
, 65536)
, 65536
,
DS.INITIAL_STG * TS.BLOCKSIZE
, S.INIEXTS * TS.BLOCKSIZE)
,
DECODE(BITAND(IP.FLAGS
, 65536)
, 65536
,
DS.NEXT_STG * TS.BLOCKSIZE
, S.EXTSIZE * TS.BLOCKSIZE)
,
DECODE(BITAND(IP.FLAGS
, 65536)
, 65536
, DS.MINEXT_STG
, S.MINEXTS)
,
DECODE(BITAND(IP.FLAGS
, 65536)
, 65536
, DS.MAXEXT_STG
, S.MAXEXTS)
,
DECODE(BITAND(IP.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(IP.FLAGS
, 65536)
, 65536
, DS.PCTINC_STG
, S.EXTPCT))
,
DECODE(BITAND(TS.FLAGS
, 32)
, 32
, TO_NUMBER(NULL)
,
DECODE(BITAND(IP.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(IP.FLAGS
, 65536)
, 65536
,
DECODE(DS.MAXINS_STG
, 0
, 1
, DS.MAXINS_STG)
,
DECODE(S.GROUPS
, 0
, 1
, S.GROUPS)))
,
DECODE(MOD(TRUNC(IP.FLAGS / 4)
, 2)
, 0
, 'YES'
, 'NO')
,
CASE
WHEN BITAND(IP.FLAGS
, 1024) = 1024 THEN 'ENABLED'
ELSE
CASE WHEN (BITAND(IP.FLAGS
, 65536) = 65536) THEN
DECODE(BITAND(DS.FLAGS_STG
, 4)
, 4
,
DECODE(BITAND(DS.CMPFLAG_STG
, 6)
,
4
, 'ADVANCED LOW'
,
2
, 'ADVANCED HIGH'
,
NULL)
,
'DISABLED')
ELSE
DECODE(BITAND(S.SPARE1
, 2048)
, 2048
,
DECODE(BITAND(S.SPARE1
, 16777216 + 1048576)
,
16777216
, 'ADVANCED HIGH'
,
1048576
, 'ADVANCED LOW'
,
NULL)
,
'DISABLED')
END
END
,
IP.BLEVEL
, IP.LEAFCNT
, IP.DISTKEY
, IP.LBLKKEY
, IP.DBLKKEY
,
IP.CLUFAC
, IP.ROWCNT
, IP.SAMPLESIZE
, IP.ANALYZETIME
,
DECODE(BITAND(DECODE(BITAND(IP.FLAGS
, 65536)
, 65536
, DS.BFP_STG
, S.CACHEHINT)
, 3)
,
1
, 'KEEP'
, 2
, 'RECYCLE'
, 'DEFAULT')
,
DECODE(BITAND(DECODE(BITAND(IP.FLAGS
, 65536)
, 65536
, DS.BFP_STG
, S.CACHEHINT)
, 12)/4
,
1
, 'KEEP'
, 2
, 'NONE'
, 'DEFAULT')
,
DECODE(BITAND(DECODE(BITAND(IP.FLAGS
, 65536)
, 65536
, DS.BFP_STG
, S.CACHEHINT)
, 48)/16
,
1
, 'KEEP'
, 2
, 'NONE'
, 'DEFAULT')
,
DECODE(BITAND(IP.FLAGS
, 8)
, 0
, 'NO'
, 'YES')
, IP.PCTTHRES$
,
DECODE(BITAND(IP.FLAGS
, 16)
, 0
, 'NO'
, 'YES')
, ''
, ''
,
DECODE(BITAND(IP.FLAGS
, 32768)
, 32768
, 'YES'
, 'NO')
,
DECODE(BITAND(IP.FLAGS
, 65536)
, 65536
, 'NO'
, 'YES')
,
DECODE(BITAND(IP.FLAGS
, 262144)
, 262144
, 'YES'
, 'NO')
FROM OBJ$ IO
, INDPART$ IP
, TS$ TS
, SYS.SEG$ S
, IND$ I
, SYS.USER$ U
, TAB$ T
,
SYS.DEFERRED_STG$ DS
WHERE IO.OBJ# = IP.OBJ#
AND TS.TS# = IP.TS#
AND IP.FILE#=S.FILE#(+) AND
IP.BLOCK#=S.BLOCK#(+)
AND IP.TS#=S.TS#(+)
AND IO.OWNER# = U.USER# AND
I.OBJ# = IP.BO#
AND I.BO# = T.OBJ#
AND IP.OBJ# = DS.OBJ#(+) AND
BITAND(IP.FLAGS
, 8388608) = 0
AND /* FILTER OUT HIDDEN PARTITIONS */
BITAND(T.TRIGFLAG
, 1073741824) != 1073741824 AND
I.TYPE# != 8
AND /* NOT LOB INDEX */
I.TYPE# != 9
AND /* NOT DOMAIN INDEX */
IO.NAMESPACE = 4
AND IO.REMOTEOWNER IS NULL
AND IO.LINKNAME IS NULL AND
(IO.OWNER# = USERENV('SCHEMAID')
OR
I.BO# IN (SELECT OBJ#
FROM OBJAUTH$
WHERE GRANTEE# IN ( SELECT KZSROROL
FROM X$KZSRO
)
)
OR
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
SELECT U.NAME
, IO.NAME
, 'YES'
, IO.SUBNAME
, ICP.SUBPARTCNT
,
ICP.HIBOUNDVAL
, ICP.HIBOUNDLEN
,
ROW_NUMBER() OVER (PARTITION BY U.NAME
, IO.NAME ORDER BY ICP.PART#)
,
'N/A'
, TS.NAME
,
ICP.DEFPCTFREE
, ICP.DEFINITRANS
, ICP.DEFMAXTRANS
,
ICP.DEFINIEXTS
, ICP.DEFEXTSIZE
, ICP.DEFMINEXTS
, ICP.DEFMAXEXTS
,
ICP.DEFMAXSIZE
, ICP.DEFEXTPCT
, ICP.DEFLISTS
, ICP.DEFGROUPS
,
DECODE(ICP.DEFLOGGING
, 0
, 'NONE'
, 1
, 'YES'
, 2
, 'NO'
, 'UNKNOWN')
,
CASE
WHEN BITAND(ICP.SPARE3
, 8) = 8 THEN 'ADVANCED LOW'
WHEN BITAND(ICP.SPARE3
, 4) = 4 THEN 'ADVANCED HIGH'
ELSE
DECODE(BITAND(ICP.FLAGS
, 1024)
, 1024
, 'ENABLED'
, 'DISABLED')
END
,
ICP.BLEVEL
, ICP.LEAFCNT
, ICP.DISTKEY
, ICP.LBLKKEY
, ICP.DBLKKEY
,
ICP.CLUFAC
, ICP.ROWCNT
, ICP.SAMPLESIZE
, ICP.ANALYZETIME
,
DECODE(BITAND(ICP.DEFBUFPOOL
, 3)
, 1
, 'KEEP'
, 2
, 'RECYCLE'
, 'DEFAULT')
,
DECODE(BITAND(ICP.DEFBUFPOOL
, 12)/4
, 1
, 'KEEP'
, 2
, 'NONE'
, 'DEFAULT')
,
DECODE(BITAND(ICP.DEFBUFPOOL
, 48)/16
, 1
, 'KEEP'
, 2
, 'NONE'
, 'DEFAULT')
,
DECODE(BITAND(ICP.FLAGS
, 8)
, 0
, 'NO'
, 'YES')
, TO_NUMBER(NULL)
,
DECODE(BITAND(ICP.FLAGS
, 16)
, 0
, 'NO'
, 'YES')
, ''
, ''
,
DECODE(BITAND(ICP.FLAGS
, 32768)
, 32768
, 'YES'
, 'NO')
, 'N/A'
, 'N/A'
FROM OBJ$ IO
, INDCOMPART$ ICP
, TS$ TS
, IND$ I
, USER$ U
, TAB$ T
WHERE IO.OBJ# = ICP.OBJ#
AND ICP.DEFTS# = TS.TS# (+)
AND IO.OWNER# = U.USER# AND
I.OBJ# = ICP.BO#
AND I.BO# = T.OBJ# AND
BITAND(T.TRIGFLAG
, 1073741824) != 1073741824 AND
BITAND(ICP.FLAGS
, 8388608) = 0
AND /* FILTER OUT HIDDEN PARTITIONS */
I.TYPE# != 8
AND /* NOT LOB INDEX */
I.TYPE# != 9
AND /* NOT DOMAIN INDEX */
IO.NAMESPACE = 4
AND IO.REMOTEOWNER IS NULL
AND IO.LINKNAME IS NULL AND
(IO.OWNER# = USERENV('SCHEMAID')
OR
I.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
SELECT U.NAME
, IO.NAME
, 'NO'
, IO.SUBNAME
, 0
,
IP.HIBOUNDVAL
, IP.HIBOUNDLEN
,
ROW_NUMBER() OVER (PARTITION BY U.NAME
, IO.NAME ORDER BY IP.PART#)
,
DECODE(BITAND(IP.FLAGS
, 1)
, 1
, 'UNUSABLE'
,
DECODE(BITAND(IP.FLAGS
, 4096)
, 4096
, 'INPROGRS'
, 'USABLE'))
,
NULL
, IP.PCTFREE$
, IP.INITRANS
, IP.MAXTRANS
,
0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
,
DECODE(MOD(TRUNC(IP.FLAGS / 4)
, 2)
, 0
, 'YES'
, 'NO')
,
'N/A'
,
IP.BLEVEL
, IP.LEAFCNT
, IP.DISTKEY
, IP.LBLKKEY
, IP.DBLKKEY
,
IP.CLUFAC
, IP.ROWCNT
, IP.SAMPLESIZE
, IP.ANALYZETIME
,
'DEFAULT'
, 'DEFAULT'
, 'DEFAULT'
,
DECODE(BITAND(IP.FLAGS
, 8)
, 0
, 'NO'
, 'YES')
, IP.PCTTHRES$
,
DECODE(BITAND(IP.FLAGS
, 16)
, 0
, 'NO'
, 'YES')
,
DECODE(I.TYPE#
,
9
, DECODE(BITAND(IP.FLAGS
, 8192)
, 8192
, 'FAILED'
, 'VALID')
,
'')
,
IPP.PARAMETERS
,
DECODE(BITAND(IP.FLAGS
, 32768)
, 32768
, 'YES'
, 'NO')
,
DECODE(BITAND(IP.FLAGS
, 65536)
, 65536
, 'NO'
, 'YES')
,
DECODE(BITAND(IP.FLAGS
, 262144)
, 262144
, 'YES'
, 'NO')
FROM OBJ$ IO
, INDPART$ IP
, IND$ I
, SYS.USER$ U
, INDPART_PARAM$ IPP
, TAB$ T
WHERE IO.OBJ# = IP.OBJ#
AND IO.OWNER# = U.USER# AND
I.OBJ# = IP.BO#
AND IP.OBJ# = IPP.OBJ# AND
I.BO# = T.OBJ#
AND BITAND(T.TRIGFLAG
, 1073741824) != 1073741824 AND
IO.NAMESPACE = 4
AND IO.REMOTEOWNER IS NULL
AND IO.LINKNAME IS NULL AND
(IO.OWNER# = USERENV('SCHEMAID')
OR
I.BO# IN (SELECT OBJ#
FROM OBJAUTH$
WHERE GRANTEE# IN ( SELECT KZSROROL
FROM X$KZSRO
)
)
OR
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 */)
)
)
|
|
|