Indexes.toRebuild.sql

<- Scripts

-- Indexes.toRebuild.sql
-- Purpose: Identify any indexes that need to be rebuilt.
-- Comments: This is derived from Doc ID 1373415.1.

set linesize 200
set pagesize 9999

COL index_name FORMAT a35 HEADING 'Index_Name'
COL index_type FORMAT a25 HEADING 'Index_Type'
COL owner      FORMAT a15 HEADING 'Owner'
COL status     FORMAT a15 HEADING 'Status'
COL table_name FORMAT a35 HEADING 'Table_Name'

prompt 
prompt +--------------------+
prompt | Indexes To Rebuild |
prompt +--------------------+
prompt

PROMPT -- BLEVEL Check
SELECT owner, index_name, table_name, blevel 
FROM dba_indexes 
WHERE BLEVEL > 3;
PROMPT

PROMPT -- Ratio Check
SELECT name, height, lf_rows, del_lf_rows, (del_lf_rows/lf_rows)*100 as ratio 
FROM INDEX_STATS;