Hey all, Now that we've upgraded to 9.2.0.5.0, I'm looking to see which indexes = are not being used over time, thanks to the new feature. Trying to = monitor this is a pain using the V$OBJECT_USAGE view (which itself is an = oddity -- where's the V_$?) because it'll only show indexes in the = current schema that are already being monitored. I'd like to see = indexes for the whole DB so I can see which ones are not being monitored = as well. So I came up with this SQueaL, based on V$OBJECT_USAGE: SELECT u.name "OWNER", io.name "INDEX_NAME", t.name "TABLE_NAME", DECODE(bitand(i.flags, 65536), 0, 'NO', 'YES') "MONITORING", DECODE(bitand(NVL(ou.flags,0), 1), 0, 'NO', 'YES') "USED", ou.start_monitoring "START_MONITORING", ou.end_monitoring "END_MONITORING" FROM sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou, sys.user$ = u WHERE t.obj# =3D i.bo# AND io.owner# =3D u.user# AND io.obj# =3D i.obj# AND u.name NOT IN ('SYS','SYSTEM') AND i.obj# =3D ou.obj#(+); The output looks to be correct on our test DBs, but I'd like to get some = feedback from y'all as to any possible gotchas. Thanks! Rich Rich Jesse System/Database Administrator rich.jesse@xxxxxxxxxxxxxxxxx QuadTech, Sussex, WI USA ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------