#!/usr/local/bin/perl -w
#
#  $Id: irebuild,v 2.0 2000/02/29 22:45:47 oracle Exp oracle $
#
#  This perl script will re-create Oracle indexes
#  for all non SYS or SYSTEM schemas in the
#  database.  It will check that it executes
#  during the time limits specified.  If there is
#  a problem during execution of the script it
#  writes the Oracle error on STDERR and continues.
#
#  Copyright (c) 2000, Brad Christensen.  All rights reserved.
#
#  Syntax:
#     irebuild --sid <SID> --uid <UID> --pwd <PWD> --maxtm <MAXTIME> [--debug]
#
#  -------------------------------------------------------------------
#  D I S C L A I M E R * * D I S C L A I M E R * * D I S C L A I M E R
#  -------------------------------------------------------------------
#  This script is free software.  Use it at your own risk.  This script
#  may be redistributed under the same terms as PERL itself. 
#  The latest version of the script may be found at:
#  http://www.andrews.edu/~bradc/perl/irebuild
#  -------------------------------------------------------------------
#

$| = 1;
use Oraperl;
use Getopt::Long;
use strict( "vars" );

use constant SUCCESS => 0;
use constant FAILURE => 1;

my $sid     = '';
my $maxtm   = 0;
my $uid     = '';
my $pwd     = '';
my $debug   = 0;

my %OPTNCTL = ( 'sid'   => \$sid,
                'maxtm' => \$maxtm,
                'uid'   => \$uid,
                'pwd'   => \$pwd,
                'debug' => \$debug );

my $res = GetOptions( \%OPTNCTL,
                      'sid=s',
                      'maxtm=i',
                      'uid=s',
                      'pwd=s',
                      'debug' );
die &Syntax() if ( scalar @ARGV );
die &Syntax() unless( $sid && $maxtm && $uid && $pwd );

###################################################################

$ENV{ORACLE_SID} = $sid;
$ENV{ORACLE_HOME} = `/u/cc/aucc/orahome $sid`;

my $login = ora_login( $sid, $uid, $pwd )
   || die "Couldn't connect to $sid as user $uid!";

my $sql = '';

$sql = "select owner,
               index_name,
               table_owner,
               table_name,
               tablespace_name,
               decode( uniqueness,
                        'NONUNIQUE', null,
                        uniqueness )
          from dba_indexes
         where owner not in ( 'SYS', 'SYSTEM' )
         order by owner, index_name";

print STDERR "Running in debug mode...\n" if ( $debug );

my $csr = ora_open( $login, $sql ) ||
   die $ora_errstr;

# now, loop through indexes we're going to rebuild
# and quit when done or when time is up
# if done, then reset schema & index to null
# if time up, save next schema & index as starting
# point for next script run...
my $STARTTIME = time;
my $STOPTIME = $STARTTIME + $maxtm;
my $lastkey = '';
my $rsql = '';
my $indexes = 0;

while( my( $iown, $inm, $town, $tnm, $tbsp, $uniq, $colnm, $seq ) =
      ora_fetch( $csr ) ) {
   $uniq ||= '';
   $uniq .= ' ';
   my $newkey = $iown . "\t" . $inm;
   if ( $newkey ne $lastkey ) {
      if ( $lastkey ) {
         if ( time >= $STOPTIME ) {
            print STDOUT "Time limit exceeded.\n",
                         "$indexes indexes rebuilt for $sid in " .
                         ( time - $STARTTIME ) . " second(s)\n";
            ora_close( $csr );
            exit( 0 );
         }
         if ( $debug ) {
            print STDERR $rsql . "\n";
         }
         else {
            ora_do( $login, $rsql ) ||
               ScriptError( $ora_errstr );
         }
         $indexes++;
      }
      $rsql = "alter index $iown.$inm rebuild unrecoverable tablespace $tbsp";
      $lastkey = $newkey;
   }
}

ora_close( $csr );

if ( $lastkey ) {
   if ( $debug ) {
      print STDERR $rsql . "\n";
   }
   else {
      ora_do( $login, $rsql ) ||
         ScriptError( $ora_errstr );
   }
   $indexes++;
}
print STDOUT "$indexes indexes rebuilt for $sid in " .
             ( time - $STARTTIME ) . " second(s).\n";

exit 0;

sub ScriptError
{
   my( $err ) = @_;

   my $msg = join( "\n", $err, '-' x 15 );
   warn $msg;
}

sub Syntax
{
   my $cmd = `/bin/basename $0`;
   chomp( $cmd );

   print STDERR "\nSyntax: $cmd --sid <SID> --maxtm <MAXTM>\n",
                "        ",
                ' ' x length( $cmd ) . " --uid <UID> --pwd <PWD>\n\n",
                "\t  <SID> = Oracle instance to connect to\n",
                "\t<MAXTM> = maximum # of seconds to allow $cmd to run\n",
                "\t  <UID> = user id of schema to connect to\n",
                "\t  <PWD> = password for schema\n\n";
   exit( -1 );
}

__END__

=pod
=head1 NAME

irebuild - Oracle Index Rebuild utility

=head1 SYNOPSIS

irebuild --sid <SID> --uid <UID> --pwd <PWD> --maxtm <MAXTM> [--debug] 

=head1 DESCRIPTION

irebuild is a PERL script that will allow the DBA to
rebuild the non-SYS and non-SYSTEM schema indexes
in an Oracle instance.  It generates and executes the
ALTER INDEX REBUILD commands in UNRECOVERABLE mode
(since that speeds up index rebuilding).  If a problem
is encountered during the index rebuilding operation,
the script logs the Oracle error message to STDERR.
and continues.

You should probably run irebuild from a crontab entry
at a particular time each day/week.  You should also
configure the maximum time allowed for the script to
execute so that it is not rebuilding indexes during
times when the database is available to users.

When run with the debug option, irebuild will NOT
rebuild the indexes, it will just generate the SQL
that would be executed to STDERR.

=head1 AUTHOR

Brad Christensen, bradc@andrews.edu

=head1 COPYRIGHT

Copyright (c) 2000, Brad Christensen.
All rights reserved.

=head1 AVAILABILITY

The latest release of irebuild can be found at
http://www.andrews.edu/~bradc/irebuild/

=cut