How can I find out what, if any, indexes are set up on the oracle data dictionary tables themselves (eg on the columns of all_tables or all_source)?
+2
A:
ALL_TABLES and ALL_SOURCE are actually views. If you have the privileges you can view the source of them:
SQL> select text from all_views where view_name='ALL_TABLES';
TEXT
--------------------------------------------------------------------------------
select u.name, o.name,decode(bitand(t.property, 2151678048), 0, ts.name, null),
decode(bitand(t.property, 1024), 0, null, co.name),
decode((bitand(t.property, 512)+bitand(t.flags, 536870912)),
0, null, co.name),
decode(bitand(t.trigflag, 1073741824), 1073741824, 'UNUSABLE', 'VALID'),
decode(bitand(t.property, 32+64), 0, mod(t.pctfree$, 100), 64, 0, null),
decode(bitand(ts.flags, 32), 32, to_number(NULL),
decode(bitand(t.property, 32+64), 0, t.pctused$, 64, 0, null)),
decode(bitand(t.property, 32), 0, t.initrans, null),
decode(bitand(t.property, 32), 0, t.maxtrans, null),
s.iniexts * ts.blocksize,
decode(bitand(ts.flags, 3), 1, to_number(NULL),
s.extsize * ts.blocksize),
s.minexts, s.maxexts,
decode(bitand(ts.flags, 3), 1, to_number(NULL),
s.extpct),
decode(bitand(ts.flags, 32), 32, to_number(NULL),
decode(bitand(o.flags, 2), 2, 1, decode(s.lists, 0, 1, s.lists))),
decode(bitand(ts.flags, 32), 32, to_number(NULL),
decode(bitand(o.flags, 2), 2, 1, decode(s.groups, 0, 1, s.groups))),
decode(bitand(t.property, 32+64), 0,
decode(bitand(t.flags, 32), 0, 'YES', 'NO'), null),
decode(bitand(t.flags,1), 0, 'Y', 1, 'N', '?'),
t.rowcnt,
decode(bitand(t.property, 64), 0, t.blkcnt, null),
decode(bitand(t.property, 64), 0, t.empcnt, null),
decode(bitand(t.property, 64), 0, t.avgspc, null),
t.chncnt, t.avgrln, t.avgspc_flb,
decode(bitand(t.property, 64), 0, t.flbcnt, null),
lpad(decode(t.degree, 32767, 'DEFAULT', nvl(t.degree,1)),10),
lpad(decode(t.instances, 32767, 'DEFAULT', nvl(t.instances,1)),10),
lpad(decode(bitand(t.flags, 8), 8, 'Y', 'N'),5),
decode(bitand(t.flags, 6), 0, 'ENABLED', 'DISABLED'),
t.samplesize, t.analyzetime,
decode(bitand(t.property, 32), 32, 'YES', 'NO'),
decode(bitand(t.property, 64), 64, 'IOT',
decode(bitand(t.property, 512), 512, 'IOT_OVERFLOW',
decode(bitand(t.flags, 536870912), 536870912, 'IOT_MAPPING', null
))),
decode(bitand(o.flags, 2), 0, 'N', 2, 'Y', 'N'),
decode(bitand(o.flags, 16), 0, 'N', 16, 'Y', 'N'),
decode(bitand(t.property, 8192), 8192, 'YES',
decode(bitand(t.property, 1), 0, 'NO', 'YES')),
decode(bitand(o.flags, 2), 2, 'DEFAULT',
decode(s.cachehint, 0, 'DEFAULT', 1, 'KEEP', 2, 'RECYCLE', NULL)),
decode(bitand(t.flags, 131072), 131072, 'ENABLED', 'DISABLED'),
decode(bitand(t.flags, 512), 0, 'NO', 'YES'),
decode(bitand(t.flags, 256), 0, 'NO', 'YES'),
decode(bitand(o.flags, 2), 0, NULL,
decode(bitand(t.property, 8388608), 8388608,
'SYS$SESSION', 'SYS$TRANSACTION')),
decode(bitand(t.flags, 1024), 1024, 'ENABLED', 'DISABLED'),
decode(bitand(o.flags, 2), 2, 'NO',
decode(bitand(t.property, 2147483648), 2147483648, 'NO',
decode(ksppcv.ksppstvl, 'TRUE', 'YES', 'NO'))),
decode(bitand(t.property, 1024), 0, null, cu.name),
decode(bitand(t.flags, 8388608), 8388608, 'ENABLED', 'DISABLED'),
decode(bitand(t.property, 32), 32, null,
decode(bitand(s.spare1, 2048), 2048, 'ENABLED', 'DISABLED')),
decode(bitand(o.flags, 128), 128, 'YES', 'NO')
from sys.user$ u, sys.ts$ ts, sys.seg$ s, sys.obj$ co, sys.tab$ t, sys.obj$ o,
sys.obj$ cx, sys.user$ cu, x$ksppcv ksppcv, x$ksppi ksppi
where o.owner# = u.user#
and o.obj# = t.obj#
and bitand(t.property, 1) = 0
and bitand(o.flags, 128) = 0
and t.bobj# = co.obj# (+)
and t.ts# = ts.ts#
and t.file# = s.file# (+)
and t.block# = s.block# (+)
and t.ts# = s.ts# (+)
and (o.owner# = userenv('SCHEMAID')
or o.obj# 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 */,
-48 /* INSERT ANY TABLE */,
-49 /* UPDATE ANY TABLE */,
-50 /* DELETE ANY TABLE */)
)
)
and t.dataobj# = cx.obj# (+)
and cx.owner# = cu.user# (+)
and ksppi.indx = ksppcv.indx
and ksppi.ksppinm = '_dml_monitoring_enabled'
As you can see, there are many tables referenced there, including SYS.OBJ$. Now you can look up the indexes on those:
SQL> select index_name from all_indexes where table_name='OBJ$' and owner='SYS';
INDEX_NAME
------------------------------
I_OBJ1
I_OBJ2
I_OBJ3
and so on.
Tony Andrews
2010-02-26 10:23:48
+1
A:
To tag on to Tony Andrew's answer, many of the tables in the data dictionary are stored in B*tree clusters:
(as sys as sysdba)
SQL> SELECT TABLE_NAME, CLUSTER_NAME FROM USER_TABLES
1 WHERE CLUSTER_NAME IN (SELECT CLUSTER_NAME FROM USER_CLUSTERS)
2 ORDER BY CLUSTER_NAME;
TABLE_NAME CLUSTER_NAME
------------------------------ ------------------------------
CCOL$ C_COBJ#
CDEF$ C_COBJ#
UET$ C_FILE#_BLOCK#
SEG$ C_FILE#_BLOCK#
SLOG$ C_MLOG#
MLOG$ C_MLOG#
VIEWTRCOL$ C_OBJ#
ICOLDEP$ C_OBJ#
LIBRARY$ C_OBJ#
OPQTYPE$ C_OBJ#
REFCON$ C_OBJ#
NTAB$ C_OBJ#
TYPE_MISC$ C_OBJ#
ATTRCOL$ C_OBJ#
SUBCOLTYPE$ C_OBJ#
COLTYPE$ C_OBJ#
LOB$ C_OBJ#
TAB$ C_OBJ#
CLU$ C_OBJ#
COL$ C_OBJ#
ICOL$ C_OBJ#
IND$ C_OBJ#
HISTGRM$ C_OBJ#_INTCOL#
RGROUP$ C_RG#
RGCHILD$ C_RG#
TYPE$ C_TOID_VERSION#
PARAMETER$ C_TOID_VERSION#
ATTRIBUTE$ C_TOID_VERSION#
RESULT$ C_TOID_VERSION#
METHOD$ C_TOID_VERSION#
COLLECTION$ C_TOID_VERSION#
FET$ C_TS#
TS$ C_TS#
TSQ$ C_USER#
USER$ C_USER#
SMON_SCN_TIME SMON_SCN_TO_TIME
So, for example, there's an "undocumented" index on both FET$ (free extents) and $TS (tablespaces) on the TS# column.
Adam Musch
2010-02-26 15:27:14