Title: Rebuilding Indices Rather Than Re-creating Them

Author: Val Soreno a Systems Architect in Victoria, British Columbia, with a reminder from
Jeff Klopping, an associate DBA in Omaha, Nebraska.

RENDX_ALL.SQL rebuilds all indices of the logged-in schema. Rebuilding indices allow you to re-locate your indeces to
another tablespace as well as improve performance. Rebuilding indices is considered better than dropping and then re-creating
them because rebuilding is much faster and downtime is almost nil. RENDX_ALL.SQL also leaves you a trace of what it
performed on file 'TMP_RENDX_ALL_.SQL' which helps when you are working on multiple instances.

/* RENDX_ALL.SQL  --  This SQL script
   Rebuilds and/or migrates all indices to a specified tablespace;
faster than re-creating them.
   Please modify 'indexes01' to match your destination tablespace.
   Questions/Contributions?  Email: Ivalson_Soreno@dmr.ca  */
-- HISTORY:
-- 10/23/98 - Val Soreno   - Ivalson_Soreno@dmr.ca  - Created and used
in multiple-instances environment.
-- 10/30/98 - Sorin Serban - Sorin.Serban@connex.ro - Contributed
concept on use of PL/SQL variables.
-- 11/02/98 - Johnson Ho   - jonson@cm.com.cn         - Contributed use
of 'select distinct' for compound indeces
SET TERM ON;
SET LINES 200;
SET PAGES 0;
SET FEED OFF;
SET VER OFF;
prompt Re-indexing all tables ...
SET TERM OFF;
column var new_value RENDX_ALL_VAR
select 'tmp_rendx_all_'||name||'.sql' as var from sys.v_$database;
column var clear
spool &RENDX_ALL_VAR
select distinct 'alter index '||index_name||' rebuild tablespace
indexes01'||chr(59) from user_ind_columns;
spool off;
@&RENDX_ALL_VAR
SET TERM ON;
prompt Re-indexing all tables complete.
set pages 60;
SET LINES 79;
SET FEED ON;
-- Line 28: END OF RENDX_ALL.SQL