oracle判断index是否存在,Oracle监控index是否被使用

从9i起，ORACLE提供了监控索引是否使用的功能，可以查看v$object_usage来观察索引是否被使用，不过查看这个视图之前需要打开索引的监控功能,使用如下命令可以打开索引监控功能 alter index schema.index_name monitoring usage;使用如下命令关闭索引监控

alter index schema.index_name monitoring usage;

SQL> select * from v$object_usage;

INDEX_NAME                     TABLE_NAME                     MON USE START_MONITORING    END_MONITORING

------------------------------ ------------------------------ --- --- ------------------- ------------------

PK_DEPT                        DEPT                           YES NO  12/03/2009 16:10:12

PK_EMP                         EMP                            YES YES 12/03/2009 16:10:26

其中use 如果为yes，表示该索引已经使用了，如果为no，表示索引未被使用。

不过这个视图让人郁闷，它只能查看当前用户的索引是否被使用:例如

SQL> show user

USER 为 "SYS"

SQL> select * from v$object_usage;

未选定行

SQL> select * from scott.v$object_usage;

select * from scott.v$object_usage

*

第 1 行出现错误:

ORA-00942: 表或视图不存在

SQL> conn scott/tiger

已连接。

SQL> select * from v$object_usage;

INDEX_NAME                     TABLE_NAME                     MON USE START_MONITORING    END_MONITORING

------------------------------ ------------------------------ --- --- ------------------- ----------------

PK_DEPT                        DEPT                           YES NO  12/03/2009 16:10:12

PK_EMP                         EMP                            YES YES 12/03/2009 16:10:26

我们可以创建一个试图，查看所有索引的使用状况

CREATE OR REPLACE VIEW SYS.V$ALL_OBJECT_USAGE

(

OWNER,

INDEX_NAME,

TABLE_NAME,

MONITORING,

USED,

START_MONITORING,

END_MONITORING

)

AS

select u.name, 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, sys.user$ u

where i.obj# = ou.obj#

and io.obj# = ou.obj#

and t.obj# = i.bo#

and io.owner# = u.user#;

SQL> CREATE OR REPLACE VIEW SYS.V$ALL_OBJECT_USAGE

2  (

3  OWNER,

4  INDEX_NAME,

5  TABLE_NAME,

6  MONITORING,

7  USED,

8  START_MONITORING,

9  END_MONITORING

10  )

11  AS

12  select u.name, io.name, t.name,

13  decode(bitand(i.flags, 65536), 0, 'NO', 'YES'),

14  decode(bitand(ou.flags, 1), 0, 'NO', 'YES'),

15  ou.start_monitoring,

16  ou.end_monitoring

17  from sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou, sys.user$ u

18  where i.obj# = ou.obj#

19  and io.obj# = ou.obj#

20  and t.obj# = i.bo#

21  and io.owner# = u.user#;

视图已创建。

SQL> select * from v$all_object_usage;

OWNER                          INDEX_NAME                     TABLE_NAME                     MON USE START_MONITORING    END_MONITORING

------------------------------ ------------------------------ ------------------------------ --- --- ------------------- -------------------

SCOTT                          PK_DEPT                        DEPT                           YES NO  12/03/2009 16:10:12

SCOTT                          PK_EMP                         EMP                            YES YES 12/03/2009 16:10:26

值得注意的是: 如果你才执行了alter index index_name monitoring usage; 然后没有用户访问过有该索引的表，那么v$object_usage.use 为no.

SQL> alter index scott.pk_dept monitoring usage;

索引已更改。

SQL> select * from v$all_object_usage;

OWNER                          INDEX_NAME                     TABLE_NAME                     MON USE START_MONITORING    END_MONITORING

------------------------------ ------------------------------ ------------------------------ --- --- ------------------- -----------------

SCOTT                          PK_DEPT                        DEPT                           YES NO  12/03/2009 16:58:37

SCOTT                          PK_EMP                         EMP                            YES YES 12/03/2009 16:10:26

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31448824/viewspace-2136623/，如需转载，请注明出处，否则将追究法律责任。