Re: Need SQL to select and recreate indexes in database

From: Suhen Pather <pathers5_at_telkom.co.za>
Date: Fri, 07 Jul 2000 08:40:58 +0200
Message-Id: <10550.111398@fatcity.com>

Hi=20

Here is a script to generate the DDL for the creation of indexes. I use it all the time it works fine.
Easier than using the indexfile option for export/import. I have taken it from Thomas Biju's home page (www.bijoos.com/oracle)=20 Go to this site there are a lot of handy scripts It however does not work for partitioned indexes.

rem
rem Script to create index creation DDL
rem
rem Biju Thomas
rem
rem Provide the owner name and table name along with the script with a = space
rem
set serveroutput on feedback off verify off pages 0 spool /tmp/crindex.sql
declare

     wuser varchar2 (15) :=3D '&1';
     wtable varchar2 (30) :=3D '&2';


     /* Indexes */

     cursor cind is
     select owner, table_owner, table_name, index_name, ini_trans, =
max_trans,
            tablespace_name, initial_extent/1024 initial_extent,=20
            next_extent/1024 next_extent, min_extents, max_extents,=20
            pct_increase, decode(uniqueness,'UNIQUE','UNIQUE') unq
     from dba_indexes
     where table_owner like upper(wuser) and
           table_name like upper(wtable);


     /* Index columns */

     cursor ccol (o in varchar2, t in varchar2, i in varchar2) is
     select decode(column_position,1,'(',',')||
               rpad(column_name,40) cl
     from dba_ind_columns
     where table_name =3D upper(t) and
           index_name =3D upper(i) and
           index_owner =3D upper(o)
     order by column_position;
     wcount number :=3D 0;

begin
  dbms_output.enable(100000);
  for rind in cind loop

     wcount :=3D wcount + 1;
       dbms_output.put_line('create '||rind.unq||' index '|| rind.owner || =
'.' || rind.index_name||' on  '||rind.table_owner||'.'|| rind.table_name);
       for rcol in ccol (rind.owner, rind.table_name, rind.index_name) =
loop
         dbms_output.put_line(rcol.cl);
       end loop;
       dbms_output.put_line(') initrans ' || rind.ini_trans || ' maxtrans =
' || rind.max_trans);
       dbms_output.put_line('tablespace ' || rind.tablespace_name);
       dbms_output.put_line('storage (initial ' || rind.initial_extent || =
'K next ' || rind.next_extent || 'K pctincrease ' || rind.pct_increase);
       dbms_output.put_line('minextents ' || rind.min_extents || ' =

maxextents '
|| rind.max_extents || ' )');

       dbms_output.put_line('/');
     end loop;
     if wcount =3D0 then
       dbms_output.put_line('**********************************************=
********');
       dbms_output.put_line('*                                             =
       *');
       dbms_output.put_line('* Plese Verify Input Parameters... No Matches =
Found! *');
       dbms_output.put_line('*                                             =
       *');
       dbms_output.put_line('**********************************************=
********');
     end if;

   end;
/
set serveroutput off feedback on verify on pages 999 spool off
prompt
prompt Output saved at /tmp/crindex.sql

Regards
$uhen Pather
Oracle DBA
Telkom SA

>>> Cherie_Machler_at_gelco.com 07/06/00 09:35PM >>> Can anyone send me a copy of a SQL script I can use to read from indexes from my database and generate DDL for recreating my indexes. I lost the script some time ago and it'll take a while to get the settings right at = the
top of the script.

Thanks in advance,

Cherie

--=20
Author:=20
  INET: Cherie_Machler_at_gelco.com=20

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may Received on Fri Jul 07 2000 - 01:40:58 CDT