Refresh Oracle Index Stats and output idx related info

OracleIdxStatsDump

set serveroutput on
set pages 0
set feedback off
set echo off
DECLARE
        height          index_stats.height%TYPE;
        del_lf_rows_len index_stats.del_lf_rows_len%TYPE;
        lf_rows_len     index_stats.lf_rows_len%TYPE;
        del_perc        number;
        table_name      user_indexes.index_name%TYPE;
        sql_stmt        varchar2(100);
        dbname          varchar2(20);
        wday            varchar2(11);
Begin
        dbms_output.enable(50000);

        select name into dbname from v$database;
        select to_char(sysdate,'dd-Mon-yyyy') into wday from dual;
        dbms_output.put_line('Index Rebuild Report for '||dbname||' on '||wday);
        dbms_output.put_line('*****');
        dbms_output.put_line('Rebuilding indexes for '||user);
        dbms_output.put_line('*****');

        for t in (select index_name from user_indexes) loop
                sql_stmt := 'alter index '||t.index_name||' rebuild compute statistics';
                execute immediate sql_stmt ;
		sql_stmt := 'analyze index '||t.index_name||' validate structure';
                execute immediate sql_stmt ;

                execute immediate 'select height, del_lf_rows_len,lf_rows_len from index_stats'
                        into height, del_lf_rows_len, lf_rows_len;
		dbms_output.put_line(t.index_name||' - height = ' || height || '. del_lf_rows_len = ' || del_lf_rows_len || '. lf_rows_len = ' || lf_rows_len);
        end loop;
End;
/