在Oracle9i中,如何监视索引并清除监视信息

SQL> connect scott/tiger@connerConnected 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>

[oracle@jumper oradata]$ sqlplus "/ as sysdba"SQL*Plus: Release 9.2.0.4.0 - ProdUCtion on Sat Dec 4 10:09:30 2004Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning option
JServer Release 9.2.0.4.0 - ProductionSQL> alter index SYS.I_OBJAUTH1 monitoring usage ;Index altered.SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.Total System Global Area 80811208 bytes
Fixed Size 451784 bytes
Variable Size 37748736 bytes
Database Buffers 41943040 bytes
Redo Buffers 667648 bytes
Database mounted. 

[oracle@jumper bdump]$ tail -f alert_conner.log
Completed: ALTER DATABASE MOUNT
Sat Dec 4 10:09:49 2004
ALTER DATABASE OPEN
Sat Dec 4 10:09:49 2004
LGWR: Primary database is in CLUSTER CONSISTENT mode
Thread 1 opened at log sequence 54
Current log# 2 seq# 54 mem# 0: /opt/oracle/oradata/conner/redo02.log
Successful open of redo thread 1.
Sat Dec 4 10:09:49 2004
SMON: enabling cache recovery
Sat Dec 4 10:10:33 2004
Restarting dead background process QMN0
QMN0 started with pid=9

[oracle@jumper oradata]$ rm -rf conner
[oracle@jumper oradata]$ cp -R connerbak/ conner
[oracle@jumper oradata]$ sqlplus '/ as sysdba'SQL*Plus: Release 9.2.0.4.0 - Production on Sat Dec 4 10:19:07 2004Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.Connected to an idle instance.SQL> startup
ORACLE instance started.Total System Global Area 80811208 bytes
Fixed Size 451784 bytes
Variable Size 37748736 bytes
Database Buffers 41943040 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL>

create or replace view v$object_usage
(index_name, table_name, monitoring, used, start_monitoring, end_monitoring)
as
select 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_monitoring
from sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou
where io.owner# = userenv('SCHEMAID')
and i.obj# = ou.obj#
and io.obj# = ou.obj#
and t.obj# = i.bo#
/

SQL> select * from v$object_usage;INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING------------------------------ ------------------------------ --- --- ------------------- -------------------PK_DEPT DEPT NO YES 10/28/2004 10:55:19 10/28/2004 10:55:47SQL> select * from object_usage;select * from object_usage *ERROR at line 1:ORA-00942: table or view does not existSQL> connect /as sysdbaConnected.SQL> / OBJ# FLAGS START_MONITORING END_MONITORING---------- ---------- ------------------- ------------------- 6288 1 10/28/2004 10:55:19 10/28/2004 10:55:47

 SQL> delete from object_usage;1 row deleted.SQL> commit;Commit complete.SQL> select * from v$object_usage;no rows selected