Never-Ending Index Question

[tjdad]

Who has scripts of sql to build sql for index objects? I'm well aware of show=y but the format is ugly. I'd prefer to extract directly off the data dictionary.

[Shestakov]

May be this procedure help to you :

---------------------------------------------------------
drop procedure system.reing_ind;

create or replace
procedure system.reing_ind(p_owner varchar2, p_drop varchar2 default 'Y') is

str varchar2(500);
str_fields varchar2(500);
sep char;
ti char(1) ;
rev varchar2(15);
tind varchar2(25);
begin

dbms_output.put_line('-- ===============================================================================');
dbms_output.put_line('-- Recreate table indexes of ' ||p_owner|| ' schema on '||
to_char(sysdate,'dd-mon-yy hh12:mi:ss'));
dbms_output.put_line('-- ===============================================================================');
dbms_output.new_line;

for ri in (select *
from sys.dba_indexes
where owner = p_owner and
index_type in ('NORMAL',
'NORMAL/REV',
'BITMAP')
)
loop
--
-- Check index type (and exclude Primary key and Unique indexes)
--
ti := check_const(p_owner, ri.index_name);
if ti = '*' then
--
if p_drop = 'Y' then
str := 'drop index '||p_owner||'.'||ri.index_name;
dbms_output.put_line(str);
dbms_output.put_line('/');
end if;
--
-- Set addition index characteristics
--
str_fields := get_ind_fields( p_owner, ri.index_name );
if ri.index_type = 'NORMAL/REV' then
rev := 'REVERSE';
else
rev := '';
end if;
if ri.index_type = 'BITMAP' then
tind := 'BITMAP';
elsif ri.uniqueness = 'UNIQUE' then
tind := 'UNIQUE';
else
tind := '';
end if;
str := 'create '||tind||' index '|| p_owner ||'.'||ri.index_name ||chr(10) ||
'on '||str_fields || rev ;
dbms_output.put_line(str);

str := chr(9) || 'tablespace '||ri.tablespace_name || chr(10) ||
chr(9) || 'pctfree '||ri.pct_free;
dbms_output.put_line(str);

str := chr(9) || 'storage ( initial '|| ri.initial_extent || chr(10) ||
chr(9) || ' next '|| ri.next_extent || chr(10) ||
chr(9) || ' minextents '|| rtrim(to_char(ri.min_extents)) || chr(10) ||
chr(9) || ' maxextents '|| rtrim(to_char(ri.max_extents)) || chr(10) ||
chr(9) || ' pctincrease '|| ri.pct_increase || chr(10) ||
chr(9) || ' )' || chr(10) || '/';
dbms_output.put_line(str);
end if;
end loop;
return;
end;
/
show errors;