--------------------------------------------------------------------------- SET ECHO OFF; SET FEEDBACK OFF; SET VERIFY OFF; SET PAGESIZE 0; SET TERMOUT ON; SET HEADING OFF; ACCEPT username CHAR PROMPT ''''Enter the index username: ''''; spool /oracle/rebuild_&username.sql;
col name heading ''''Index Name'''' format a30 col del_lf_rows heading ''''Deleted|Leaf Rows'''' format 99999999 col lf_rows_used heading ''''Used|Leaf Rows'''' format 99999999 col ratio heading ''''% Deleted|Leaf Rows'''' format 999.99999
SELECT name, del_lf_rows, lf_rows - del_lf_rows lf_rows_used, to_char(del_lf_rows / (lf_rows)*100,''''999.99999'''') ratio FROM index_stats where name = upper(''''&index_name'''');