sql> connect scott/[email protected] to oracle9i enterprise edition release 9.2.0.4.0 connected as scottsql> select index_name from user_indexes;index_name------------------------------pk_deptpk_emp开始监视pk_dept索引:sql> alter index pk_dept monitoring usage;index altered在此过程中,如果查询使用索引,将会记录下来:sql> select * from dept where deptno=10;deptno dname loc------ -------------- ------------- 10 accounting new york停止监视:sql> alter index pk_dept nomonitoring usage;index altered查询索引使用情况,yes表示在监视过程中索引被使用到:sql> select * from v$object_usage;index_name table_name monitoring used start_monitoring end_monitoring----------------- ------------------ ---------- ---- ------------------- -------------------pk_dept dept no yes 10/28/2004 10:55:19 10/28/2004 10:55:47sql>
total system global area 80811208 bytesfixed size 451784 bytesvariable size 37748736 bytesdatabase buffers 41943040 bytesredo buffers 667648 bytesdatabase mounted.
此时，数据库挂起，而且不会有任何提示，在alert<sid>.log文件中，你可以看到:
[[email protected] bdump]$ tail -f alert_conner.log completed: alter database mountsat dec 4 10:09:49 2004alter database opensat dec 4 10:09:49 2004lgwr: primary database is in cluster consistent modethread 1 opened at log sequence 54 current log# 2 seq# 54 mem# 0: /opt/oracle/oradata/conner/redo02.logsuccessful open of redo thread 1.sat dec 4 10:09:49 2004smon: enabling cache recoverysat dec 4 10:10:33 2004restarting dead background process qmn0qmn0 started with pid=9
然后数据库将会停在此处。
如果不知道此bug存在，你可能会一筹莫展的。
现在你能做的就是从备份中恢复，或者升级到9.2.0.5。
oracle已经release了这个bug，你可以参考metalink:note:2934068.8,oracle声明在9.2.0.5 (server patch set)和 10g production base release中fixed了这个bug。
sql*plus: release 9.2.0.4.0 - production on sat dec 4 10:19:07 2004
copyright (c) 1982, 2002, oracle corporation. all rights reserved.
connected to an idle instance.
sql> startuporacle instance started.
total system global area 80811208 bytesfixed size 451784 bytesvariable size 37748736 bytesdatabase buffers 41943040 bytesredo buffers 667648 bytesdatabase mounted.database opened.sql>
3. 在特殊的情况下,你可能需要清除这个v$object_usage视图中的信息.
oracle的说法是,在下一次收集该对象的索引使用情况时会自动覆盖上一次的信息,不提供清除手段.
稍微研究了一下.
v$object_usage是基于以下基表建立起来的:
create or replace view v$object_usage(index_name, table_name, monitoring, used, start_monitoring, end_monitoring)asselect io.name, t.name, decode(bitand(i.flags, 65536), 0, 'no', 'yes'), decode(bitand(ou.flags, 1), 0, 'no', 'yes'), ou.start_monitoring, ou.end_monitoringfrom sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ouwhere io.owner# = userenv('schemaid') and i.obj# = ou.obj# and io.obj# = ou.obj# and t.obj# = i.bo#/
