This script will spool out the index create statement with the true size of the index. variable block_size number begin select to_number(value) into :block_size from v$parameter where name = 'db_block_size'; end; / drop table t_size; create table t_size (owner varchar2(30) ,index_name varchar2(30) ,used_blocks number(9) ) tablespace users storage (initial 64K next 64K pctincrease 0) pctfree 0 pctused 80; declare v_cursorid integer; v_dummy integer; v_indx varchar2(30); v_next_extent number; v_owner varchar2(30); cursor c1 is select owner,index_name,next_extent from dba_indexes where owner not in ('SYS','SYSTEM'); -- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -- find true size of each index - store in t_size -- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - begin v_cursorid := dbms_sql.open_cursor; open c1; loop fetch c1 into v_owner,v_indx,v_next_extent; exit when c1%notfound; dbms_sql.parse(v_cursorid,'analyze index '||v_owner||'.'||v_indx|| ' validate structure',dbms_sql.v7); v_dummy := dbms_sql.execute(v_cursorid); insert into t_size select v_owner,name,lf_blks+br_blks from index_stats where ((blocks-lf_blks-br_blks)*:block_size)> v_next_extent; end loop; dbms_sql.close_cursor(v_cursorid); close c1; commit; end; / -- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -- spool out the create scripts -- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - spool c:\clients\pg\alter_indexes.sql select 'alter index '||a.owner||'.'||a.index_name|| ' rebuild tablespace '|| tablespace_name|| chr(10)|| ' storage(initial '||(a.used_blocks*:block_size)||' next '|| b.next_extent|| ' maxextents '||b.max_extents||')'|| ' unrecoverable;' from t_size a ,dba_indexes b where a.owner = b.owner and a.index_name = b.index_name; spool off @c:\clients\pg\alter_indexes.sql drop table t_size;