SQL> select index_name,monitoring,used,start_monitoring,end_monitoring
from v$object_usage;
INDEX_NAME MONITORING USED START_MONITORING END_MONITORING
------------ ---------- ------ -------------------- --------------------
PRODUCTS_PK NO NO 11/28/2005 11:31:26 11/28/2005 11:36:16
PK_EMP YES NO 11/30/2005 16:59:09
PK_DEPT YES NO 11/28/2005 11:43:50
IX1_EMP NO NO 11/30/2005 13:42:48 11/30/2005 13:46:38
SQL> select OBJ#,object_name,ANALYZETIME, FLAGS
from ind$ i, dba_objects o
where o.object_type = 'INDEX' and i.obj# = o.object_id
and o.owner = 'SCOTT';
create or replace view hc$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.user$ u, sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou
where i.obj# = ou.obj#
and io.obj# = ou.obj#
and t.obj# = i.bo#
and u.user# = io.owner#;
select * from hc$ALL_OBJECT_USAGE where owner = 'SCOTT';
OWNER INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING
------ ------------- ------------ ------------ ------ ----------------- ---------------
SCOTT PRODUCTS_PK PRODUCTS NO NO 11/28/2005 11:31:26 11/28/2005 11:36:16
SCOTT PK_EMP EMP NO YES 11/30/2005 16:59:09 11/30/2005 17:24:35
SCOTT PK_DEPT DEPT YES NO 11/28/2005 11:43:50
SCOTT IX1_EMP EMP NO NO 11/30/2005 13:42:48 11/30/2005 13:46:38