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;

How to recreate an Oracle table index

Leave a Reply

Your email address will not be published. Required fields are marked *

9 + one =