SQL Script Recently Accessed Indexes

Updated : 14-Mar-2002
Version : 1.0

Description

When maintaining the database, it is sometimes useful to know which indexes were most recently accessed by users.

Parameters

None

SQL Source

set serverout on size 1000000
set verify off

column owner format a20 trunc
column segment_name format a30 trunc

spool bufferidx.lst

select distinct b.owner, b.segment_name
from x$bh a, dba_extents b         
where b.file_id=a.dbarfil 
and a.dbablk between b.block_id 
and b.block_id+blocks-1 
and segment_type='INDEX' and  b.owner not in ('SYS','SYSTEM')
/

spool off

Return to Index of SQL Scripts

Home | Company Profile | Services | Contact Us | SQL scripts and tips | Quiz Legal