×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Oracle: All versions FAQ

Scripts

rebuildIndexes.sh by Michael42
Posted: 17 Oct 06

CODE

#!/bin/sh
# Purpose:    Rebuild Indexes for Entire Database
# Version:    2.01
# OS:         Sun Solaris 8-10
# Oracle:     9i-10g
# Usage:      Change User Variables as required to match your environment.
# -----------------------------------------------------------------------------------------
# EXCEPTIONS  Schema not to rebuild indexes on. Example: EXCEPTIONS="'SYS',"SYSTEM'"
###########################################################################################

###############################################
# Init User Variables
###############################################
# Init Oracle Env
ORACLE_SID="DB1"
ORACLE_BASE="/home/oracle"
ORACLE_HOME="$ORACLE_BASE/product/10.2/db"
LD_LIBRARY_PATH="$ORACLE_HOME/lib"

EXCEPTIONS="'ANONYMOUS','DBSNMP','DIP','EXFSYS','MGMT_VIEW','OUTLN','ORACLE','OPS\$ORACLE','PUBLIC','SYS','SYSTEM','SYSMAN','TSMSYS','WMSYS','XDB'"


###########################################################################################
###########################################################################################
#####################     DO NOT CHANGE ANYTHING BELOW THIS LINE    #######################
###########################################################################################
###########################################################################################

###############################################
# Init System Vars - Common
###############################################
PATH=/bin:$PATH
SCRIPTNAME=`basename $0 .sh`
DATE_START=`date`
DATE_STAMP=`date '+%Y%m%d'`
TIME_STAMP=`date '+%H%M'`


###############################################
# Header
###############################################
clear
echo "===> $SCRIPTNAME.sh Started\n"
echo "     ORACLE_BASE:        $ORACLE_BASE"
echo "     ORACLE_HOME:        $ORACLE_HOME"
echo "     LD_LIBRARY_PATH:    $LD_LIBRARY_PATH"
echo "     TNS_ADMIN:        $TNS_ADMIN"
echo "     ORACLE_SID:        $ORACLE_SID"
echo "     EXCEPTIONS:        $EXCEPTIONS"


###############################################
# Create Initial Script (.tmp)
###############################################
echo "\n\n===> Creating Initial Script: $SCRIPTNAME.tmp \n"
$ORACLE_HOME/bin/sqlplus "/ as sysdba" << SQLPLUS_SESSION
SET ECHO OFF
SET HEADING OFF
SET VERIFY OFF
SET TERMOUT OFF
SET TIMING OFF
spool /tmp/$SCRIPTNAME.tmp
SELECT 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' REBUILD ONLINE NOLOGGING COMPUTE STATISTICS TABLESPACE '||TABLESPACE_NAME||';'
FROM ALL_USERS, ALL_INDEXES
WHERE ALL_INDEXES.OWNER = ALL_USERS.USERNAME
AND INDEX_NAME NOT LIKE '%$%'
AND ALL_USERS.USERNAME NOT IN ($EXCEPTIONS)
ORDER BY owner,index_name;
spool off
exit
SQLPLUS_SESSION


###############################################
# Clean Up Script (yank out junk syntax)
###############################################
echo "\n===> Preporocessing Script..."
cat /tmp/$SCRIPTNAME.tmp|tail +7|grep -i 'ALTER INDEX' > /tmp/$SCRIPTNAME.sql
rm -rf /tmp/$SCRIPTNAME.tmp


###############################################
# Run Script to Rebuild Indexes
###############################################
echo "\n===> Running Script..."
$ORACLE_HOME/bin/sqlplus "/ as sysdba" << SQLPLUS_SESSION
SET ECHO ON
SET HEADING ON
SET TERMOUT ON
@/tmp/$SCRIPTNAME.sql
exit
SQLPLUS_SESSION


###############################################
# End
###############################################
DATE_END=`date`
echo "\n\n===> $SCRIPTNAME.sh Ended"
echo "        Start Time:  $DATE_START"
echo "       End Time:    $DATE_END\n"

Back to Oracle: All versions FAQ Index
Back to Oracle: All versions Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close