45 lines
1.1 KiB
SQL
45 lines
1.1 KiB
SQL
@@header
|
|
|
|
set term off
|
|
/*
|
|
*
|
|
* Author : Vishal Gupta
|
|
* Purpose : Display index usage monitoring
|
|
* Parameters : NONE
|
|
*
|
|
*
|
|
* Revision History:
|
|
* ===================
|
|
* Date Author Description
|
|
* --------- ------------ -----------------------------------------
|
|
* 12-Mar-12 Vishal Gupta Intial version
|
|
*
|
|
*
|
|
*/
|
|
set term on
|
|
|
|
COLUMN table_owner HEADING "Table Owner" FORMAT a20
|
|
COLUMN table_name HEADING "Table Name" FORMAT a20
|
|
COLUMN index_name HEADING "Index Name" FORMAT a20
|
|
COLUMN Monitoring HEADING "Monitoring" FORMAT a10
|
|
COLUMN Used HEADING "Used" FORMAT a4
|
|
|
|
|
|
select t.name TABLE_NAME
|
|
, io.name INDEX_NAME
|
|
, decode(bitand(i.flags, 65536), 0, 'NO', 'YES') MONITORING
|
|
, decode(bitand(ou.flags, 1), 0, 'NO', 'YES') USED
|
|
, ou.start_monitoring
|
|
, ou.end_monitoring
|
|
from 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#
|
|
;
|
|
|
|
|
|
@@footer
|