RE: Rebuilding Indexes...

[Stephen Lee]

Title: 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
 -- 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
 -- 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