- 如果你打开监控索引的情况下,通过v$object_usage视图可以知道索引是否使用,这为我们删除多余的索引很有帮助
- SQL> desc v$object_usage;
- Name Null? Type
- ----------------------------------------- -------- ----------------------------
- INDEX_NAME NOT NULL VARCHAR2(30)
- TABLE_NAME NOT NULL VARCHAR2(30)
- MONITORING VARCHAR2(3)
- USED VARCHAR2(3)
- START_MONITORING VARCHAR2(19)
- END_MONITORING VARCHAR2(19)
- --打开索引监控
- SQL> alter index idx_test_oid monitoring usage;
- Index altered.
- --可以看在此视图中有一条记录,但use字段显示为no,表示此索引还未使用
- SQL> select * from v$object_usage;
- INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING
- ------------------------------ ------------------------------ --- --- ------------------- ----------
- IDX_TEST_OID TEST YES NO 05/06/2007 20:35:56
- SQL>
- SQL>
- --使用索引
- SQL> select * from test where object_id=1;
- no rows selected
- --这时我们看到use字段已变成YES,表时此索引自监控以来,被使用过,但使用的次数不知道。
- SQL> select * from v$object_usage;
- INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING
- ------------------------------ ------------------------------ --- --- ------------------- ----------
- IDX_TEST_OID TEST YES YES 05/06/2007 20:35:56
- SQL>
- SQL>
- --使用以下语法停止索引监控
- SQL> alter index idx_test_oid nomonitoring usage;
- Index altered.
- SQL> select * from v$object_usage;
- INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING
- ------------------------------ ------------------------------ --- --- ------------------- ----------
- IDX_TEST_OID TEST NO YES 05/06/2007 20:35:56 05/06/2007 20:40:35
- --那我们如何删除这些监控记录了
- SQL> delete from object_usage;
- delete from object_usage
- *
- ERROR at line 1:
- ORA-00942: table or view does not exist
- --这个视图建立在sys用户下的object_usage 上
- SQL> conn sys/sys as sysdba
- Connected.
- SQL> delete from object_usage;
- 1 row deleted.
- SQL> commit;
- Commit complete.
- --我们再次查询,发现没有记录了
- SQL> conn test/test
- Connected.
- SQL> select * from v$object_usage;
- no rows selected