rem ----------------------------------------------------------------------- rem Filename: idxrecr8.sql rem Purpose: Script to spool a listing of all drop and create statements rem required to rebuild indexes. rem Notes: Will build all non-sys/system indexes rem Date: 10-Oct-1998 rem Author: Anonymous rem ----------------------------------------------------------------------- set linesize 80; set pagesize 10000; set long 50; set trimspool on; set termout off; set echo off; set feedback off; col dummy noprint format a1; col dummy2 noprint format a1; col index_name noprint format a1; col command format a80 wrap word; spool bld_ndx2.rslt /* ********************************* */ /* 1. Generate drop index commands */ /* 2. Generate create index commands */ /* ********************************* */ select 'drop index '||owner||'.'||index_name||';' command, 0 dummy, index_name, 0 dummy2 from all_indexes where table_owner NOT in ('SYS', 'SYSTEM') UNION select 'create'||decode(UNIQUENESS, 'UNIQUE', ' UNIQUE', NULL)||' index '||owner||'.'||index_name||' on ' ||table_owner||'.'||table_name||' (' command, 1 dummy, index_name, 0 dummy2 from all_indexes where table_owner NOT in ('SYS', 'SYSTEM') UNION select ' '||decode(column_position,1,null,',')||column_name||' ASC' command, 2 dummy, index_name, column_position dummy2 from all_ind_columns where table_owner NOT in ('SYS', 'SYSTEM') AND all_ind_columns.index_name in (select index_name from all_indexes where table_owner NOT in ('SYS', 'SYSTEM')) UNION select ' )' command, 3 dummy, index_name, 0 dummy2 from all_ind_columns where table_owner NOT in ('SYS', 'SYSTEM') AND all_ind_columns.index_name in (select index_name from all_indexes where table_owner NOT in ('SYS', 'SYSTEM')) UNION select ' pctfree '||pct_free||' initrans '||ini_trans||' maxtrans '||max_trans||' storage (initial '||initial_extent||' next '||next_extent||' minextents '||min_extents||' maxextents '||max_extents||' pctincrease '||pct_increase||' ) tablespace '||tablespace_name||';' command, 4 dummy, index_name, 0 dummy2 from all_indexes where table_owner NOT in ('SYS', 'SYSTEM') order by 3,2,4 / spool off; set heading on; set pagesize 100; set termout on;