crea_index.sql

REM
REM                      SCRIPT FOR CREATING INDEXES
REM
REM This script must be run by a user with the DBA role.
REM
REM Running this script will in turn create a script to build all the
REM indexes in the database.  This created script, create_index.sql,
REM can be run by any user with the DBA role or with the 'CREATE ANY INDEX' 
REM system privilege.
REM
REM The script will NOT capture the indexes created by the user 'SYS'.
REM
REM
set verify off;
set termout off;
set feedback off;
set echo off;
set pagesize 0;
set termout on
select 'Creating index build script...' from dual;
set termout off;
create table i_temp 
     (lineno NUMBER, id_owner VARCHAR2(30), id_name VARCHAR2(30), 
      text VARCHAR2(800))
/
DECLARE
   CURSOR ind_cursor IS select   owner, 
                                 index_name,
                                 table_owner, 
                                 table_name,
                                 uniqueness,
                                 tablespace_name,
                                 ini_trans,
                                 max_trans,
                                 initial_extent,
                                 next_extent,
                                 min_extents,
                                 max_extents,
                                 pct_increase,
                                 pct_free
                        from     dba_indexes
                        where owner != 'SYS'
                        order by index_name;
   CURSOR col_cursor (i_own VARCHAR2, c_ind VARCHAR2, c_tab VARCHAR2) IS
               select   column_name
               from     dba_ind_columns
               where    index_owner = i_own
                 and    index_name = c_ind
                 and    table_name = c_tab
                order   by column_position;
   v_index_owner       dba_indexes.owner%TYPE;
   v_index_name        dba_indexes.index_name%TYPE;
   v_table_owner       dba_indexes.table_owner%TYPE;
   v_table_name        dba_indexes.table_name%TYPE;
   v_uniqueness        dba_indexes.uniqueness%TYPE;
   v_tablespace_name   dba_indexes.tablespace_name%TYPE;
   v_ini_trans         dba_indexes.ini_trans%TYPE;
   v_max_trans         dba_indexes.max_trans%TYPE;
   v_initial_extent    dba_indexes.initial_extent%TYPE;
   v_next_extent       dba_indexes.next_extent%TYPE;
   v_min_extents       dba_indexes.min_extents%TYPE;
   v_max_extents       dba_indexes.max_extents%TYPE;
   v_pct_increase      dba_indexes.pct_increase%TYPE;
   v_pct_free          dba_indexes.pct_free%TYPE;
   v_column_name       dba_ind_columns.column_name%TYPE;
   v_first_rec         BOOLEAN;
   v_string            VARCHAR2(800);
   v_lineno            number := 0;
 
   procedure write_out(p_line INTEGER, p_owner varchar2, p_name VARCHAR2, 
                       p_string VARCHAR2) is
   begin
      insert into i_temp (lineno,id_owner, id_name,text) 
             values (p_line,p_owner,p_name,p_string);
   end;
 
BEGIN
   OPEN ind_cursor;
   LOOP
      FETCH ind_cursor INTO     v_index_owner,
                                v_index_name,
                                v_table_owner,
                                v_table_name,
                                v_uniqueness,
                                v_tablespace_name,
                                v_ini_trans,
                                v_max_trans,
                                v_initial_extent,
                                v_next_extent,
                                v_min_extents,
                                v_max_extents,
                                v_pct_increase,
                                v_pct_free;
      EXIT WHEN ind_cursor%NOTFOUND;
        v_lineno := 1;
      v_first_rec := TRUE;
      if (v_uniqueness = 'UNIQUE') then
         v_string:= 'CREATE UNIQUE INDEX ' || lower(v_index_owner) || '.' ||
                     lower(v_index_name);
         write_out(v_lineno, v_index_owner, v_index_name, v_string);
         v_lineno := v_lineno + 1;
      else
         v_string:= 'CREATE INDEX ' || lower(v_index_owner) || '.' ||
                     lower(v_index_name);
         write_out(v_lineno, v_index_owner, v_index_name, v_string);
         v_lineno := v_lineno + 1;
      end if;
      OPEN col_cursor(v_index_owner,v_index_name,v_table_name);
      LOOP
         FETCH col_cursor INTO v_column_name;
         EXIT WHEN col_cursor%NOTFOUND;
         if (v_first_rec) then
            v_string := '   ON '|| lower(v_table_owner) || '.' || 
                         lower(v_table_name)||' (';
         v_first_rec := FALSE;
         else
            v_string := v_string || ',';
         end if;
         v_string := v_string || lower(v_column_name);
      END LOOP;
      CLOSE col_cursor;
      v_string := v_string || ')';
      write_out(v_lineno, v_index_owner, v_index_name, v_string);
      v_lineno := v_lineno + 1;
      v_string := null;
      v_string := 'PCTFREE ' || to_char(v_pct_free);
      write_out(v_lineno, v_index_owner, v_index_name, v_string);
      v_lineno := v_lineno + 1;
      v_string := 'INITRANS ' || to_char(v_ini_trans) ||
                  ' MAXTRANS ' || to_char(v_max_trans);
      write_out(v_lineno, v_index_owner, v_index_name, v_string);
      v_lineno := v_lineno + 1;
      v_string := 'TABLESPACE ' || v_tablespace_name || ' STORAGE (';
      write_out(v_lineno, v_index_owner, v_index_name, v_string);
      v_lineno := v_lineno + 1;
      v_string := 'INITIAL ' || to_char(v_initial_extent) ||
                   ' NEXT ' || to_char(v_next_extent);
      write_out(v_lineno, v_index_owner, v_index_name, v_string);
      v_lineno := v_lineno + 1;
      v_string := 'MINEXTENTS ' || to_char(v_min_extents) ||
                  ' MAXEXTENTS ' || to_char(v_max_extents) ||
                 ' PCTINCREASE ' || to_char(v_pct_increase) || ')';
      write_out(v_lineno, v_index_owner, v_index_name, v_string);
      v_lineno := v_lineno + 1;
      v_string := '/';
      write_out(v_li˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙