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;