Find out indexes that are not used at all in Oracle 10g R2 database.
Hi Gurus,
I am running Enterprise Edition Oracle 10.2.0.4 Database on HP-UX PARISC.
My Objective is to find out indexes that were not used at all. I surfed online forums and google and found the query. Then I made some touchs to it, to bring to this final form:
==============================================================================================
select p.object_name, p.operation, p.options, to_char(p.timestamp, 'MM/DD/YYYY HH24:MI'), count(1)
from dba_hist_sql_plan p,
dba_hist_sqlstat s
where p.object_owner <> 'SYS'
and p.operation like '%INDEX%'
and p.sql_id = s.sql_id
and object_name like 'table_name%'
I am running Enterprise Edition Oracle 10.2.0.4 Database on HP-UX PARISC.
My Objective is to find out indexes that were not used at all. I surfed online forums and google and found the query. Then I made some touchs to it, to bring to this final form:
==============================================================================================
select p.object_name, p.operation, p.options, to_char(p.timestamp, 'MM/DD/YYYY HH24:MI'), count(1)
from dba_hist_sql_plan p,
dba_hist_sqlstat s
where p.object_owner <> 'SYS'
and p.operation like '%INDEX%'
and p.sql_id = s.sql_id
and object_name like 'table_name%'
0