RE: Rebuilding Indexes...

Here is the index rebuilding part of a ksh script I use. Prior to this, I some variable setting, check the existence and permissions on some files, check the existence of tablespaces, check that the script is not already running, etc. In this script, all the index extents are set to the same size which is fine for this particular database.  

• snip ------------

echo "RAT INDEX REBUILD SCRIPT" >> "$MAILFILE" ############## First index build to alternate tablespace ########### echo "--------------- START TIME: `/usr/bin/date +'%T %D'` --------------" >> "$MAILFILE"
{
sqlplus -s <<-XXXX
  system/$SYSPASS@$ORACLE_SID <mailto:system/$SYSPASS@$ORACLE_SID>

• crap
• crap
• crap

 set serveroutput on
 whenever sqlerror exit failure  

 ALTER TABLESPACE ALT_RAT_DATA_IDX COALESCE;    declare
  cursor c1 is select owner,index_name from dba_indexes where tablespace_name = 'RAT_DATA_IDX';
  a integer;
  b integer;
 begin
  dbms_output.enable(500000);  

  for x in c1 loop
   execute immediate 'ALTER INDEX '||x.owner||'.'||x.index_name||' REBUILD INITRANS 20 STORAGE(INITIAL 1M NEXT 1M MAXEXTENTS UNLIMITED PCTINCREASE 0) TABLESPACE ALT_RAT_DATA_IDX ONLINE';
   dbms_output.put_line('ALT_RAT_DATA_IDX '||x.owner||'.'||x.index_name);   end loop;  

  select count(*) into a from dba_indexes where tablespace_name = 'RAT_DATA_IDX';
  select count(*) into b from dba_indexes where tablespace_name = 'ALT_RAT_DATA_IDX';
  dbms_output.put_line('RAT_DATA_IDX = '||a||' ALT_RAT_DATA_IDX = '||b);  

 exception
  when others then
   dbms_output.put_line('ERROR: '||SQLCODE||' '||SQLERRM);  end;
 /
XXXX
} | /usr/bin/sed '/^$/d; s/^ *//g; s/^ *$//g' | while read LINE; do   if [ -n "`echo $LINE | /usr/bin/sed -n '/^ERROR/p'`" ]; then    echo "PROBLEM encountered on first index rebuild." >> "$MAILFILE"    echo "$LINE" >> "$MAILFILE"
   while read LINE; do
    echo "$LINE" >> "$MAILFILE"
   done
   echo "$0 $* did not finished" >> "$MAILFILE"    mailx -s "BROKE: rat index rebuild" $SUPPORT < "$MAILFILE"    exit 1
  fi
  echo "$LINE" >> "$MAILFILE"
 done  

echo "------------- END FIRST REBUILD: `/usr/bin/date +'%T %D'` --------------" >> "$MAILFILE"   ############## Second index build back to original tablespace ########### {
sqlplus -s <<-XXXX
  system/$SYSPASS@$ORACLE_SID <mailto:system/$SYSPASS@$ORACLE_SID>

• crap
• crap
• crap

 set serveroutput on
 whenever sqlerror exit failure  

 ALTER TABLESPACE RAT_DATA_IDX COALESCE;    declare
  cursor c1 is select owner,index_name from dba_indexes where tablespace_name = 'ALT_RAT_DATA_IDX';
  cursor c3 is select owner,object_name from dba_objects where status = 'INVALID';
  a integer;
  b integer;
 begin
  dbms_output.enable(100000);  

  for x in c1 loop
   execute immediate 'ALTER INDEX '||x.owner||'.'||x.index_name||' REBUILD INITRANS 20 STORAGE(INITIAL 1M NEXT 1M MAXEXTENTS UNLIMITED PCTINCREASE 0) TABLESPACE RAT_DATA_IDX ONLINE';
   dbms_output.put_line('RAT_DATA_IDX '||x.owner||'.'||x.index_name);   end loop;  

  select count(*) into a from dba_indexes where tablespace_name = 'RAT_DATA_IDX';
  select count(*) into b from dba_indexes where tablespace_name = 'ALT_RAT_DATA_IDX';
  dbms_output.put_line('RAT_DATA_IDX = '||a||' ALT_RAT_DATA_IDX = '||b);  

  dbms_output.put_line('----------------------- INVALID OBJECTS
-------------------------');
 

  for x in c3 loop
   dbms_output.put_line(x.owner||'.'||x.object_name);   end loop;  

 exception
  when others then
   dbms_output.put_line('ERROR: '||SQLCODE||' '||SQLERRM);  end;
 /  

 ALTER TABLESPACE RAT_DATA_IDX COALESCE;  ALTER TABLESPACE ALT_RAT_DATA_IDX COALESCE;   XXXX
} | /usr/bin/sed '/^$/d; s/^ *//g; s/^ *$//g' | while read LINE; do   if [ -n "`echo $LINE | /usr/bin/sed -n '/^ERROR/p'`" ]; then    echo "PROBLEM encountered on second index rebuild."    echo "$LINE"
   while read LINE; do
    echo "$LINE"
   done
   exit 1
  fi
  echo "$LINE" >> "$MAILFILE"
 done  

echo "------------- END SECOND REBUILD: `/usr/bin/date +'%T %D'` --------------" >> "$MAILFILE"   mailx -s "SUCCESS: rat index rebuild" $SUPPORT < "$MAILFILE"  

exit 0

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephen Lee
  INET: slee_at_dollar.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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
also send the HELP command for other information (like subscribing).