Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Index Creation Script

Re: Index Creation Script

From: Pete Sharman <psharman_at_us.oracle.com>
Date: Thu, 30 Sep 1999 09:38:49 -0700
Message-ID: <37F39219.DFDD37FE@us.oracle.com> 





Greg



Here's a script that will do all the indexes in the database.  You can restrict
is as needed using WHERE clauses.



HTH.


Pete


Article-ID:         <Note:33398.1>
Document-ID:        <OLS:107643.754>
Folder:             server.dbms.dba
Topic:              ** Useful SQL scripts
Subject:            SQL: SCRIPT FOR CREATING INDEXES
-Author:            RADRANLY
Circulation:        ** Available to Customers on OCIS **
-Department:
Creation Date:      24 January   1996
Last Revision Date: 01 April     1996




Expiration Date:

-Revision Number:
-Distribution Code:
-Category:          RDBMS
-Product:           RDBMS
Product Version:    7.0+
Platform:           GENERIC
Information Type:   ADVISORY
Impact:             MEDIUM
Abstract:           This bulletin is a script that once run will generate
                    another script that will include all the create index
                    statements for all the indexes in the database.  The
                    remarks should be reviewed carefully before running this
                    script.
Keywords:           SCRIPT;CREATE;INDEX;DATABASE

Keywords:            SCRIPT;CREATE;INDEX;DATABASE

REM
REM                      SCRIPT FOR CREATING INDEXES


REM



REM This script must be run by a user with the DBA role.
REM



REM This script is intended to run with Oracle7.
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 The script will capture indexes for clusters, but the create statement
REM for these indexes will fail because it DOES NOT include the
REM keyword CLUSTER.  If the create index of a cluster is executed, it
REM will result in an error 'ora-942 table or view does not exist' because
REM it will be looking for a table of that name NOT A CLUSTER.
REM



REM NOTE:  Indexes automatically created by table CONSTRAINTS will also
REM be INCLUDED in the create_index.sql script.  It may cause a problem to
REM create an index with a system assigned name such as SYS_C00333.
REM



REM Only preliminary testing of this script was performed.  Be sure to test
REM it completely before relying on it.
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;

   lv_index_owner       dba_indexes.owner%TYPE;
   lv_index_name        dba_indexes.index_name%TYPE;
   lv_table_owner       dba_indexes.table_owner%TYPE;
   lv_table_name        dba_indexes.table_name%TYPE;
   lv_uniqueness        dba_indexes.uniqueness%TYPE;
   lv_tablespace_name   dba_indexes.tablespace_name%TYPE;
   lv_ini_trans         dba_indexes.ini_trans%TYPE;
   lv_max_trans         dba_indexes.max_trans%TYPE;
   lv_initial_extent    dba_indexes.initial_extent%TYPE;
   lv_next_extent       dba_indexes.next_extent%TYPE;
   lv_min_extents       dba_indexes.min_extents%TYPE;
   lv_max_extents       dba_indexes.max_extents%TYPE;
   lv_pct_increase      dba_indexes.pct_increase%TYPE;
   lv_pct_free          dba_indexes.pct_free%TYPE;
   lv_column_name       dba_ind_columns.column_name%TYPE;
   lv_first_rec         BOOLEAN;
   lv_string            VARCHAR2(800);
   lv_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     lv_index_owner,
                                lv_index_name,
                                lv_table_owner,
                                lv_table_name,
                                lv_uniqueness,
                                lv_tablespace_name,
                                lv_ini_trans,
                                lv_max_trans,
                                lv_initial_extent,
                                lv_next_extent,
                                lv_min_extents,
                                lv_max_extents,
                                lv_pct_increase,
                                lv_pct_free;
      EXIT WHEN ind_cursor%NOTFOUND;
        lv_lineno := 1;
      lv_first_rec := TRUE;
      if (lv_uniqueness = 'UNIQUE') then
         lv_string:= 'CREATE UNIQUE INDEX ' || lower(lv_index_owner) || '.' ||
                     lower(lv_index_name);
         write_out(lv_lineno, lv_index_owner, lv_index_name, lv_string);
         lv_lineno := lv_lineno + 1;
      else
         lv_string:= 'CREATE INDEX ' || lower(lv_index_owner) || '.' ||
                     lower(lv_index_name);
         write_out(lv_lineno, lv_index_owner, lv_index_name, lv_string);
         lv_lineno := lv_lineno + 1;
      end if;
      OPEN col_cursor(lv_index_owner,lv_index_name,lv_table_name);
      LOOP
         FETCH col_cursor INTO lv_column_name;
         EXIT WHEN col_cursor%NOTFOUND;
         if (lv_first_rec) then
            lv_string := '   ON '|| lower(lv_table_owner) || '.' ||
                         lower(lv_table_name)||' (';
         lv_first_rec := FALSE;
         else
            lv_string := lv_string || ',';
         end if;
         lv_string := lv_string || lower(lv_column_name);
      END LOOP;
      CLOSE col_cursor;
      lv_string := lv_string || ')';
      write_out(lv_lineno, lv_index_owner, lv_index_name, lv_string);
      lv_lineno := lv_lineno + 1;
      lv_string := null;
      lv_string := 'PCTFREE ' || to_char(lv_pct_free);
      write_out(lv_lineno, lv_index_owner, lv_index_name, lv_string);
      lv_lineno := lv_lineno + 1;
      lv_string := 'INITRANS ' || to_char(lv_ini_trans) ||
                  ' MAXTRANS ' || to_char(lv_max_trans);
      write_out(lv_lineno, lv_index_owner, lv_index_name, lv_string);
      lv_lineno := lv_lineno + 1;
      lv_string := 'TABLESPACE ' || lv_tablespace_name || ' STORAGE (';
      write_out(lv_lineno, lv_index_owner, lv_index_name, lv_string);
      lv_lineno := lv_lineno + 1;
      lv_string := 'INITIAL ' || to_char(lv_initial_extent) ||
                   ' NEXT ' || to_char(lv_next_extent);
      write_out(lv_lineno, lv_index_owner, lv_index_name, lv_string);
      lv_lineno := lv_lineno + 1;
      lv_string := 'MINEXTENTS ' || to_char(lv_min_extents) ||
                  ' MAXEXTENTS ' || to_char(lv_max_extents) ||
                 ' PCTINCREASE ' || to_char(lv_pct_increase) || ')';
      write_out(lv_lineno, lv_index_owner, lv_index_name, lv_string);
      lv_lineno := lv_lineno + 1;
      lv_string := '/';
      write_out(lv_lineno, lv_index_owner, lv_index_name, lv_string);
      lv_lineno := lv_lineno + 1;
      lv_lineno := lv_lineno + 1;
      lv_string:='                                                  ';
      write_out(lv_lineno, lv_index_owner, lv_index_name, lv_string);


   END LOOP;



   CLOSE ind_cursor;


END;



/



spool create_indexes.sql


set heading off


set recsep off


col text format a80 word_wrap



select   text


from     I_temp


order by id_owner, id_name, lineno;



spool off



drop table i_temp;



exit


REM ---------------------------------------------------------------------------
REM                                          Oracle WorldWide Customer Support








Greg Akins wrote:



> Can anybody point me to a script that will output an index


> creation script by querying an existing index?


>


> I'd like to run a block which builds a 'CREATE INDEX'


> script, drop the index and recreates the script.


>


> -greg


>


> * Sent from RemarQ http://www.remarq.com The Internet's Discussion Network *


> The fastest and easiest way to search and participate in Usenet - Free!








Received on Thu Sep 30 1999 - 11:38:49 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US