PC Professionell 2004 December/PCpro_2004_12.ISO/files/webserver/xampp/xampp-perl-addon-1.4.9-installer.exe/defrag.pl

Perl Script | 2002-06-14 | 80.7 KB | 2,703 lines

#! /usr/bin/perl -w

# $Id: defrag.pl,v 1.18 2001/04/28 13:51:28 rvsutherland Exp $
#
# Copyright (c) 2000, 2001 Richard Sutherland - United States of America
#
# See COPYRIGHT section in pod text below for usage and distribution rights.

use Cwd;
use DBI;
use DDL::Oracle;
use English;
use Getopt::Long;

use strict;

my %args;
my %uniq;

my @constraints;
my @export_objects;
my @export_temps;
my @logfiles;
my @perf_tables = (
                   'DBA_ALL_TABLES',
                   'DBA_INDEXES',
                   'DBA_PART_INDEXES',
                   'DBA_PART_TABLES',
                   'DBA_SEGMENTS',
                   'DBA_TABLES',
                   'THE_CONSTRAINTS',
                   'THE_IOTS',
                   'THE_INDEXES',
                   'THE_PARTITIONS',
                   'THE_TABLES',
                  );
my @sizing_array;

my $add_ndx_log;
my $add_tbl_log;
my $add_temp_log;
my $add_temp_sql;
my $alttblsp;
my $aref;
my $create_ndx_ddl;
my $create_tbl_ddl;
my $create_temp_ddl;
my $date;
my $dbh;
my $drop_all_log;
my $drop_ddl;
my $drop_temp_ddl;
my $drop_temp_log;
my $drop_temp_sql;
my $expdir;
my $exp_log;
my $header10;
my $home = $ENV{HOME}
        || $ENV{LOGDIR}
        || ( getpwuid( $REAL_USER_ID ) )[7]
        || die "\nCan't determine HOME directory.\n";
my $imp_log;
my $logdir;
my $obj;
my $other_constraints;
my $partitions;
my $prefix;
my $prttn_exp_log;
my $prttn_exp_par;
my $prttn_exp_text;
my $prttn_imp_log;
my $prttn_imp_par;
my $prttn_imp_text;
my $row;
my $script;
my $sqldir;
my $sth;
my $stmt;
my $tblsp;
my $text;
my $user = getlogin
        || scalar getpwuid( $REAL_USER_ID )
        || undef
     unless $OSNAME eq 'MSWin32';
$user = 'Unknown User'    unless $user;

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

set_defaults();

if (
         @ARGV    == 0
     or  $ARGV[0] eq "?"
     or  $ARGV[0] eq "-?"
     or  $ARGV[0] eq "-h"
     or  $ARGV[0] eq "--help"
   )
{
  print_help();
  exit 0;
}

print "\n$0 is being executed by $user\non ", scalar localtime,"\n\n";
get_args();
print "Generating files to defrag Tablespace $tblsp.\n",
      "Using Tablespace $alttblsp for partition operations.\n\n";
initialize_queries();

#
# Display user options, and save them in .defrag.rc
#

delete $args{ sid } if $args{ sid } eq "";
open RC, ">$home/.defragrc" or die "Can't open $home/.defragrc:  $!\n";
KEY:
  foreach my $key ( sort keys %args )
  {
    next KEY unless (
                         $key eq "sid"
                      or $key eq "logdir"
                      or $key eq "sqldir"
                      or $key eq "prefix"
                      or $key eq "expdir"
                      or $key eq "resize"
                    );
    print "$key = $args{ $key }\n";
    print RC "$key = $args{ $key }\n";
  }
close RC or die "Can't close $home/.defragrc:  $!\n";

print "\nWorking...\n\n";

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

#
# Now we're ready -- start dafriggin' defraggin'
#

# The 10 steps below issue queries mostly comprised of 5 main queries,
# sometimes doing UNIONs and/or MINUSes among them.  The query results
# are stored in temporary tables for performance reasons.
#
# See sub 'initialize_queries' for the queries and their descriptions.
#

# Step 1 - Export the stray partitions -- those in our tablespace whose
#          table also has partitions in at least one other tablespace.
#          If said partitions exist, there will be 2 exports.  After the
#          first export, for each such partition:
#            a) Create a Temp table mirroring the partition.
#            b) Create indexes on the Temp table matching the LOCAL
#               indexes on the partitioned table.
#            c) Create a PK matching the PK of the partitioned table,
#               if any.
#            d) EXCHANGE the Temp table with the partition.
#            e) MOVE the [now empty] partition to the alternate tablespace.
#
#          With the data now in the Temp table, the Temp table gets
#          treated the same as other regular tables in our tablespace
#          (see Steps 2 - 9), but has added operations following the
#          creation of its indexes (same as the LOCAL indexes on the
#          partition) and the addition of its PK (if any).
#
#            a) the Temp table does an EXCHANGE PARTITION so that the
#               data (which was imported into the Temp table) rejoins
#               the partitioned table.
#            b) the [now empty] Temp table is DROPped.
#
#            c) REBUILD all Global indexes (if any) on the partitioned
#               table(s).
#
#          NOTE:  Two 'fall back' scripts are created which are to be
#                 used ONLY in the event that problems occur during
#                 Step 1 (Shell #2 when such partitions exist).
#
#                  ***  DO NOT PROCEED IF Shell #2 HAS ERRORS ***
#
#                 Shells #8 and #9  will restore the data to the original
#                 condition Their Steps are:
#                   a) DROP the Temp table(s).
#                   b) TRUNCATE the partitions
#                   c) MOVE the partitions back to our tablespace
#                   d) Import the data back into the partitions.
#

$stmt =
      "
       SELECT
              owner
            , segment_name
            , partition_name
            , segment_type
            , partitioning_type
            , analyzed
       FROM
              THE_PARTITIONS
       ORDER
          BY
              1, 2, 3
      ";

$sth = $dbh->prepare( $stmt );
$sth->execute;
$aref = $sth->fetchall_arrayref;

foreach $row ( @$aref )
{
  my (
       $owner,
       $table,
       $partition,
       $type,
       $partitioning_type,
       $analyzed
     ) = @$row;

  $obj = DDL::Oracle->new(
                           type => 'exchange table',
                           list => [
                                     [
                                       "$owner",
                                       "$table:$partition",
                                     ]
                                   ],
                         );
  my $create_tbl = $obj->create;
  # Remove REM lines created by DDL::Oracle
  $create_tbl = ( join "\n",grep !/^REM/,split /\n/,$create_tbl )."\n\n";

  my $temp = "${tblsp}_${date}_" . unique_nbr();

  push @export_temps,   "\L$owner.$table:$partition";
  push @export_objects, "\L$owner.$temp";

  # Change the CREATE TABLE statement to create the temp
  my $ownr    = escaped_dollar_signs( $owner );
  my $tabl    = escaped_dollar_signs( $table );
  $create_tbl =~ s|\L$ownr.$tabl|\L$owner.$temp|g;

  my $exchange = index_and_exchange( $temp, @$row );

  $obj = DDL::Oracle->new(
                           type => 'table',
                           list => [
                                     [
                                       "$owner",
                                       "$temp",
                                     ]
                                   ],
                         );
  my $drop_tbl = $obj->drop;
  # Remove REM lines created by DDL::Oracle
  $drop_tbl = ( join "\n", grep !/^REM/, split /\n/, $drop_tbl ) . "\n\n";

  $obj = DDL::Oracle->new(
                           type => 'table',
                           list => [
                                     [
                                       "$owner",
                                       "$table:$partition",
                                     ]
                                   ],
                         );
  my $resize = $obj->resize;
  # Remove REM lines created by DDL::Oracle
  $resize =  ( join "\n", grep !/^REM/, split /\n/, $resize ) . "\n\n";
  $resize =~ s|\;|\nTABLESPACE \L$tblsp \;\n\n|;

  my $drop_temp =  $drop_tbl .
                   trunc( @$row ) .
                   $resize;

  $create_temp_ddl  = group_header( 1 )   unless $create_temp_ddl;
  $create_temp_ddl .= $create_tbl .
                      $exchange .
                      move( @$row, $alttblsp );

  $drop_ddl         = group_header( 2 )   unless $drop_ddl;
  $drop_ddl        .= $drop_tbl;

  $create_tbl_ddl   = group_header( 7 )   unless $create_tbl_ddl;
  $create_tbl_ddl  .= $create_tbl;

  $create_ndx_ddl   = group_header( 9 )   unless $create_ndx_ddl;
  $create_ndx_ddl  .= $exchange .
                      $drop_tbl;

  $drop_temp_ddl    = group_header( 15 )  unless $drop_temp_ddl;
  $drop_temp_ddl   .= $drop_temp;
}

#
# Step 2 - Drop all Foreign Keys referenceing our tables and IOT's or
#          referenceing the tables of our other indexes.  NOTE:  our
#          indexes may not be the target of a foreign key, but for
#          simplicity purposes if the index's table holds said target
#          (i.e., its index is in some other tablespace but it belongs
#          to the same table as our index), we'll drop the FK anyway --
#          it won't hurt anything and we promise to put it back.
#

$stmt =
      "
       SELECT --+ use_hash(c r)
              c.owner
            , c.constraint_name
       FROM
              THE_CONSTRAINTS  c
            , THE_CONSTRAINTS  r
       WHERE
                  c.constraint_type   = 'R'
              AND c.r_owner           = r.owner
              AND c.r_constraint_name = r.constraint_name
              AND (
                      r.owner
                    , r.table_name
                  ) IN (
                         SELECT
                                owner
                              , table_name
                         FROM
                                THE_TABLES
                         UNION ALL
                         SELECT
                                owner
                              , table_name
                         FROM
                                THE_IOTs
                         UNION ALL
                         SELECT
                                owner
                              , table_name
                         FROM
                                THE_INDEXES
                       )
       ORDER
          BY
              1, 2
      ";

$sth = $dbh->prepare( $stmt );
$sth->execute;
my $fk_aref = $sth->fetchall_arrayref;

$obj = DDL::Oracle->new(
                         type => 'constraint',
                         list => $fk_aref,
                       );

$drop_ddl .= group_header( 3 ) . $obj->drop    if @$fk_aref;

#
# Step 3 - Drop and create the tables.  NOTE:  the DROP statements are in
#          one file followed by COALESCE tablespace statements, and the
#          CREATE statements are put in a separate file.  The assumption
#          here is that the user will verify that the DROP and COALESCE
#          statements executed OK before executing the CREATE tables file.
#

$stmt =
      "
       SELECT DISTINCT
              owner
            , table_name
            , analyzed
       FROM
            (
              SELECT
                     owner
                   , table_name
                   , analyzed
              FROM
                     THE_TABLES
              UNION ALL
              SELECT
                     owner
                   , table_name
                   , analyzed
              FROM
                     THE_IOTs
            )
       ORDER
          BY
              1, 2
      ";

$sth = $dbh->prepare( $stmt );
$sth->execute;
$aref = $sth->fetchall_arrayref;

if ( @$aref )
{
  $obj = DDL::Oracle->new(
                           type => 'table',
                           list => $aref,
                         );

  $drop_ddl       .= group_header( 4 ) . $obj->drop;

  $create_tbl_ddl .= group_header( 8 ) . $obj->create;

  foreach $row ( @$aref )
  {
    my ( $owner, $table, $analyzed ) = @$row;

    push @export_objects, "\L$owner.$table";

    if ( $analyzed eq 'YES' )
    {
      $create_ndx_ddl .= group_header( 10 )    unless $header10++;

      $create_ndx_ddl .= "PROMPT " .
                         "ANALYZE TABLE \L$owner.$table\n\n" .
                         "ANALYZE TABLE \L$owner.$table " .
                         "ESTIMATE STATISTICS ;\n\n";
    }
  }

}

#
# Step 4 - Drop all Primary Key, Unique and Check constraints on the tables
#          of our indexes (those on our tables disappeared with the DROP
#          TABLE statements).
#

$stmt =
      "
       SELECT
              owner
            , constraint_name
       FROM
              THE_CONSTRAINTS
       WHERE
                  constraint_type IN ('P','U','C')
              AND (
                      owner
                    , table_name
                  ) IN (
                         SELECT
                                owner
                              , table_name
                         FROM
                                THE_INDEXES
                         MINUS
                         (
                           SELECT
                                  owner
                                , table_name
                           FROM
                                  THE_TABLES
                           UNION ALL
                           SELECT
                                  owner
                                , table_name
                           FROM
                                  THE_IOTs
                         )
                       )
       ORDER
          BY
              1, 2
      ";

$sth = $dbh->prepare( $stmt );
$sth->execute;
$aref = $sth->fetchall_arrayref;

$obj = DDL::Oracle->new(
                         type => 'constraint',
                         list => $aref,
                       );

$drop_ddl .= group_header( 5 ) . $obj->drop    if @$aref;

#
# Step 5 - Drop all of our indexes, unless they are the supporting index
#          of a Primary Key or Unique constraint -- these disappeared in
#          the preceding step.
#

$stmt =
      "
       SELECT
              owner
            , index_name
       FROM
              THE_INDEXES i
       WHERE
              NOT EXISTS   (
                             SELECT
                                    null
                             FROM
                                    THE_CONSTRAINTS
                             WHERE
                                        owner           = i.owner
                                    AND constraint_name = i.index_name
                           )
              AND (
                      owner
                    , table_name
                  ) NOT IN (
                             SELECT
                                    owner
                                  , table_name
                             FROM
                                    THE_TABLES
                             UNION ALL
                             SELECT
                                    owner
                                  , table_name
                             FROM
                                    THE_IOTs
                           )
       ORDER
          BY
              1, 2
      ";

$sth = $dbh->prepare( $stmt );
$sth->execute;
$aref = $sth->fetchall_arrayref;

$obj = DDL::Oracle->new(
                         type => 'index',
                         list => $aref,
                       );

$drop_ddl .= group_header( 6 ) . $obj->drop    if @$aref;

#
# Step 6 - Create ALL indexes.
#

$stmt =
      "
       SELECT
              owner
            , index_name
            , table_name
            , analyzed
       FROM
              THE_INDEXES
       ORDER
          BY
              1, 2
      ";

$sth = $dbh->prepare( $stmt );
$sth->execute;
$aref = $sth->fetchall_arrayref;

$obj = DDL::Oracle->new(
                         type => 'index',
                         list => $aref,
                       );

$create_ndx_ddl .= group_header( 10 )    unless $header10++;

$create_ndx_ddl .= $obj->create    if @$aref;

foreach $row ( @$aref )
{
  my ( $owner, $index, $table, $analyzed ) = @$row;

  if ( $analyzed eq 'YES' )
  {
    $create_ndx_ddl .= "PROMPT " .
                       "ANALYZE INDEX \L$owner.$index\n\n" .
                       "ANALYZE INDEX \L$owner.$index\n" .
                       "   ESTIMATE STATISTICS ;\n\n" .
                       "PROMPT " .
                       "ANALYZE TABLE \L$owner.$table\n\n" .
                       "ANALYZE TABLE \L$owner.$table\n" .
                       "   ESTIMATE STATISTICS " .
                       "FOR ALL INDEXED COLUMNS ;\n\n";
  }
}

#
# Step 7 - Create all Primary Key, Unique and Check constraints on our
#          tables and on the tables of our indexes.  NOTE:  do not create
#          the constraints for the IOT tables -- their primary keys were
#          defined in the CREATE TABLE statements.
#

$stmt =
      "
       SELECT
              owner
            , constraint_name
            , constraint_type
            , search_condition
       FROM
              dba_constraints
       WHERE
                  constraint_type     IN ('P','U','C')
              AND (
                      owner
                    , table_name
                  ) IN (
                         SELECT
                                owner
                              , table_name
                         FROM
                                THE_TABLES
                         UNION ALL
                         SELECT
                                owner
                              , table_name
                         FROM
                                THE_INDEXES
                       )
       ORDER
          BY
              1, 2
      ";

$dbh->{ LongReadLen } = 8192;    # Allows SEARCH_CONDITION length of 8K
$dbh->{ LongTruncOk } = 1;

$sth = $dbh->prepare( $stmt );
$sth->execute;
$aref = $sth->fetchall_arrayref;

foreach $row ( @$aref )
{
  my ( $owner, $constraint_name, $cons_type, $condition, ) = @$row;

  if ( $cons_type ne 'C' )
  {
    push @constraints, [ $owner, $constraint_name ];
  }
  elsif ( $condition !~ /IS NOT NULL/ )  # NOT NULL is part of CREATE TABLE
  {
    push @constraints, [ $owner, $constraint_name ];
  }
}

$obj = DDL::Oracle->new(
                         type => 'constraint',
                         list => \@constraints,
                       );

$create_ndx_ddl .= group_header( 11 ) . $obj->create    if @constraints;

#
# Step 8 - Create all Check constraints on our IOT tables (their PK was
#          part of the CREATE TABLE, and they can't have any other indexes,
#          thus no UK's)
#

$stmt =
      "
       SELECT
              owner
            , constraint_name
            , constraint_type
            , search_condition
       FROM
              dba_constraints
       WHERE
                  constraint_type = 'C'
              AND (
                      owner
                    , table_name
                  ) IN (
                         SELECT
                                owner
                              , table_name
                         FROM
                                THE_IOTs
                       )
       ORDER
          BY
              1, 2
      ";

$dbh->{ LongReadLen } = 8192;    # Allows SEARCH_CONDITION length of 8K
$dbh->{ LongTruncOk } = 1;

$sth = $dbh->prepare( $stmt );
$sth->execute;
$aref = $sth->fetchall_arrayref;

@constraints = ();
foreach $row ( @$aref )
{
  my ( $owner, $constraint_name, $cons_type, $condition, ) = @$row;

  if ( $condition !~ /IS NOT NULL/ )  # NOT NULL is part of CREATE TABLE
  {
    push @constraints, [ $owner, $constraint_name ];
  }
}

$obj = DDL::Oracle->new(
                         type => 'constraint',
                         list => \@constraints,
                       );

$create_ndx_ddl .= group_header( 12 ) . $obj->create    if @constraints;

#
# Step 9 - Recreate all Foreign Keys referenceing our tables and IOT's or
#          referenceing the tables of our other indexes.  Use the same list
#          used in Step 2 to drop them ($fk_aref).
#

$obj = DDL::Oracle->new(
                         type => 'constraint',
                         list => $fk_aref,
                       );

$create_ndx_ddl .= group_header( 13 ) . $obj->create    if @$fk_aref;

#
# Step 10 - REBUILD all UNUSABLE indexes/index [sub]partitions. These are
#           the non-partitioned or Global partitioned indexes on THE
#           PARTITIONS.
#

$stmt =
      "
       SELECT
              owner
            , index_name
       FROM
              dba_indexes
       WHERE
              (
                  owner
                , table_name
              ) IN (
                     SELECT
                            owner
                          , segment_name
                     FROM
                            THE_PARTITIONS
                   )
       MINUS
       SELECT              -- Ignore partitioned, LOCAL indexes
              owner
            , index_name
       FROM
              dba_part_indexes
       WHERE
              locality = 'LOCAL'
       ORDER
          BY
              1
      ";

$sth = $dbh->prepare( $stmt );
$sth->execute;
$aref = $sth->fetchall_arrayref;

$obj = DDL::Oracle->new(
                         type => 'index',
                         list => $aref,
                       );

$create_ndx_ddl .= group_header( 14 ) . $obj->resize    if @$aref;

#
# It's hard to believe, but maybe they gave us an empty tablespace
# to practice on.
#

die "\n***Error:  Tablespace $tblsp is empty.
           Doest thou take me for a fool?\n\n"
     unless $create_tbl_ddl . $create_ndx_ddl;

#
# OK, we're ligit.  Coalesce all data/index tablespaces
#

$stmt =
      "
       SELECT
              LOWER(tablespace_name)
       FROM
              dba_tablespaces  t
       WHERE
                  status            = 'ONLINE'
              AND contents         <> 'TEMPORARY'
              AND tablespace_name  <> 'SYSTEM'
              AND extent_management = 'DICTIONARY'
       MINUS
       SELECT
              LOWER(tablespace_name)
       FROM
              dba_segments
       WHERE
              segment_type    = 'ROLLBACK'
       ORDER
          BY
              1
      ";

$sth = $dbh->prepare( $stmt );
$sth->execute;
$aref = $sth->fetchall_arrayref;

foreach $row ( @$aref )
{
  $drop_ddl .= "PROMPT ALTER TABLESPACE @$row->[0] COALESCE\n\n" .
               "ALTER TABLESPACE @$row->[0] COALESCE ;\n\n",
}

# Get rid of double blank lines
$drop_ddl        =~ s|\n\n+|\n\n|g;
$drop_temp_ddl   =~ s|\n\n+|\n\n|g;
$create_tbl_ddl  =~ s|\n\n+|\n\n|g;
$create_ndx_ddl  =~ s|\n\n+|\n\n|g;
$create_temp_ddl =~ s|\n\n+|\n\n|g;

drop_perf_temps();

#
# Wrap it up -- open, write and close all files
#

if ( $create_temp_ddl )
{
  $add_temp_sql = "$sqldir/$prefix${tblsp}_add_temp.sql";
  print "Create temps            : $add_temp_sql\n";
  write_file( $add_temp_sql, $create_temp_ddl, 'REM' );

  $drop_temp_sql = "$sqldir/$prefix${tblsp}_drop_temp.sql";
  print "Drop temps              : $drop_temp_sql\n";
  write_file( $drop_temp_sql, $drop_temp_ddl, 'REM' );
}

my $drop_all_sql = "$sqldir/$prefix${tblsp}_drop_all.sql";
print "Drop objects            : $drop_all_sql\n";
write_file( $drop_all_sql, $drop_ddl, 'REM' );

my $add_tbl_sql = "$sqldir/$prefix${tblsp}_add_tbl.sql";
print "Create tables           : $add_tbl_sql\n";
write_file( $add_tbl_sql, $create_tbl_ddl, 'REM' );

my $add_ndx_sql = "$sqldir/$prefix${tblsp}_add_ndx.sql";
print "Create indexes          : $add_ndx_sql\n\n";
write_file( $add_ndx_sql, $create_ndx_ddl, 'REM' );

my $pipefile = "$expdir/$prefix$tblsp.pipe";
unlink $pipefile;
eval { system ("mknod $pipefile p") };

if ( $create_temp_ddl )
{
  $prttn_exp_par   = "$expdir/$prefix${tblsp}_prttn_exp.par";
  $prttn_exp_text  = export_par_text( $prttn_exp_log, \@export_temps);
  print "Partition Export parfile: $prttn_exp_par\n";
  print "Partition Export logfile: $prttn_exp_log\n";
  write_file( $prttn_exp_par, $prttn_exp_text, '#' );

  $prttn_imp_par  = "$expdir/$prefix${tblsp}_prttn_imp.par";
  $prttn_imp_text = import_par_text( $prttn_imp_log, \@export_temps );
  print "Partition Import parfile: $prttn_imp_par\n";
  print "Partition Import logfile: $prttn_imp_log\n\n";
  write_file( $prttn_imp_par, $prttn_imp_text, '#' );
}

my $exp_par   = "$expdir/$prefix${tblsp}_exp.par";
my $exp_text  = export_par_text( $exp_log, \@export_objects );
print "Table Export parfile    : $exp_par\n";
print "Table Export logfile    : $exp_log\n";
write_file( $exp_par, $exp_text, '#' );

my $imp_par  = "$expdir/$prefix${tblsp}_imp.par";
my $imp_text = import_par_text( $imp_log, \@export_objects );
print "Table Import parfile    : $imp_par\n";
print "Table Import logfile    : $imp_log\n\n";
write_file( $imp_par, $imp_text, '#' );

print "Export FIFO pipe        : $pipefile\n\n";

#
# And, finally, the little shell scripts to help with the driving
#

print "\n";

my $i     = 0;
my $shell = "$sqldir/$prefix$tblsp.sh";
my $gzip  = "$expdir/$prefix${tblsp}_prttn.dmp.gz";

if ( $create_temp_ddl )
{

  $script = $shell . ++$i;
  $text =
    "# Step $i -- Export the partitions in Tablespace $tblsp\n\n" .
    "nohup cat $pipefile | gzip -c \\\n" .
    "        > $gzip &\n\n" .
    "exp / parfile = $prttn_exp_par\n" .
    check_exp_log( $script, $prttn_exp_log );
  create_shell( $script, $text );

  $script = $shell . ++$i;
  $text =
    "# Step $i -- Use SQL*Plus to run $add_temp_sql\n" .
    "#           which will create temp tables for partitions " .
    "in tablespace $tblsp\n\n" .
    "sqlplus -s / << EOF\n\n" .
    "   SPOOL $add_temp_log\n\n" .
    "   @ $add_temp_sql\n\n" .
    "EOF\n" .
    check_sql_log( $script, $add_temp_log );
  create_shell( $script, $text );
}

$script = $shell . ++$i;
$text = "# Step $i -- Export the tables in Tablespace $tblsp\n\n";
if ( @export_objects )
{
  $text .=
  "nohup cat $pipefile | gzip -c \\\n" .
  "        > $gzip &\n\n" .
  "exp / parfile = $exp_par\n" .
  check_exp_log( $script, $exp_log );
}
else
{
  $text .=
  "echo\n" .
  "echo There are no Tables in tablespace $tblsp.\n" .
  "echo Skipping Export.\n" .
  "echo\n" .
  "echo $shell\n" .
  "echo completed successfully without errors.\n" .
  "echo on \` date \`\n" .
  "echo\n\n";
}
create_shell( $script, $text );

$script = $shell . ++$i;
$text =
  "# Step $i -- Use SQL*Plus to run $drop_all_sql\n" .
  "#           which will drop all objects in tablespace $tblsp\n\n" .
  "sqlplus -s / << EOF\n\n" .
  "   SPOOL $drop_all_log\n\n" .
  "   @ $drop_all_sql\n\n" .
  "EOF\n" .
  check_sql_log( $script, $drop_all_log );
create_shell( $script, $text );

$script = $shell . ++$i;
$text =
  "# Step $i -- Use SQL*Plus to run $add_tbl_sql\n".
  "#           which will recreate all tables in tablespace $tblsp\n\n" .
  "sqlplus -s / << EOF\n\n" .
  "   SPOOL $add_tbl_log\n\n" .
  "   @ $add_tbl_sql\n\n" .
  "EOF\n" .
  check_sql_log( $script, $add_tbl_log );
create_shell( $script, $text );

$script = $shell . ++$i;
$text = "# Step $i -- Import the tables back into Tablespace $tblsp\n\n";
if ( @export_objects )
{
  $text .=
  "nohup gunzip -c $gzip \\\n" .
  "              > $pipefile &\n\n" .
  "imp / parfile = $imp_par\n" .
  check_imp_log( $script, $imp_log );
}
else
{
  $text .=
  "echo\n" .
  "echo There are no Tables in tablespace $tblsp.\n" .
  "echo Skipping Import.\n" .
  "echo\n" .
  "echo $shell\n" .
  "echo completed successfully without errors.\n" .
  "echo on \` date \`\n" .
  "echo\n\n";
}
create_shell( $script, $text );

$script = $shell . ++$i;
$text =
  "# Step $i -- Use SQL*Plus to run $add_ndx_sql\n" .
  "#           which will recreate all indexes/constraints " .
  "in tablespace $tblsp\n\n" .
  "sqlplus -s / << EOF\n\n" .
  "   SPOOL $add_ndx_log\n\n" .
  "   @ $add_ndx_sql\n\n" .
  "EOF\n" .
  check_sql_log( $script, $add_ndx_log );
create_shell( $script, $text );

$text = "echo $shell is being executed by $user\n" .
        "echo on \` date \`\n\n";

foreach my $j ( 1 .. $i )
{
  $text .= "$shell$j\n\n" .
           "RC=\$?\n\n" .
           "if [ \${RC} -gt 0 ]\n" .
           "then\n\n" .
           "   echo\n" .
           "   echo\n" .
           "   echo '*** ERROR'\n" .
           "   echo $shell$j failed\n" .
           "   echo on \` date \`\n" .
           "   echo\n" .
           "   exit \${RC}\n\n" .
           "fi\n\n";
}

$text .= "echo And so did $shell\n" .
         "echo\n" .
         "echo YAHOO!!\n" .
         "echo\n" .
         "exit 0\n\n";

print "\nAnd if you want a driver script for all of the above, it is:\n\n",
      "   $shell\n\n\n";
open SHELL, ">$shell"     or die "Can't open $shell: $!\n";
write_header( \*SHELL, $shell, '# ' );
print SHELL $text . "#  --- END OF FILE ---\n\n";
close SHELL                  or die "Can't close $shell: $!\n";

if ( $create_temp_ddl )
{
  $gzip  = "$expdir/$prefix${tblsp}_prttn.dmp.gz";

  print "\n*** The following 2 scripts ARE FOR FALLBACK PURPOSES ONLY!!\n" .
        "*** Use these scripts ONLY IF Shell #2 HAD ERRORS.\n\n";

  $script = $shell . ++$i;
  $text =
    "# USE FOR FALLBACK PURPOSES ONLY\n\n" .
    "# Use SQL*Plus to run $drop_temp_sql\n" .
    "# which will drop the temp tables holding data for partitions " .
    "in tablespace $tblsp\n\n" .
    "sqlplus -s / << EOF\n\n" .
    "   SPOOL $drop_temp_log\n\n" .
    "   @ $drop_temp_sql\n\n" .
    "EOF\n" .
    check_sql_log( $script, $drop_temp_log );
  create_shell( $script, $text );

  $script = $shell . ++$i;
  $text =
    "# USE FOR FALLBACK PURPOSES ONLY\n\n" .
    "#Import the tables back into the partitions in " .
    "Tablespace $tblsp\n\n" .
    "echo\n" .
    "echo \"**************** NOTICE ***************\"\n" .
    "echo\n" .
    "echo Ignore warnings about missing partitions -- because not\n" .
    "echo all partitions were exported, and thus not all partitions\n" .
    "echo need be re-imported.\n" .
    "echo The error to be ignored is:\n" .
    "echo\n" .
    "echo \"  IMP-00057: Warning: Dump file may not contain data of all partitions...\"\n" .
    "echo\n" .
    "echo \"************ END OF NOTICE ************\"\n\n" .
    "nohup gunzip -c $gzip \\\n" .
    "              > $pipefile &\n\n" .
    "imp / parfile = $prttn_imp_par\n" .
    check_imp_log( $script, $prttn_imp_log );
  create_shell( $script, $text );
}

my @shells = glob( "$sqldir/$prefix$tblsp.sh*" );
chmod( 0754, @shells ) == @shells or die "\nCan't chmod some shells: $!\n";

print "\n$0 completed successfully\non ", scalar localtime,"\n\n";

exit 0;

#################### Subroutines (alphabetically) ######################

# sub check
#
# returns text for a shell script to check its LOG file for errors
#
sub check
{
  my ($shell, $log ) = @_;

  return
"then

   echo
   echo '*** ERRORS during'
   echo $shell
   echo
   echo CHECK LOG $log
   echo
   exit 1

else

   echo
   echo $shell
   echo completed successfully without errors.
   echo on \` date \`
   echo

fi

";

}

# sub check_exp_log
#
# returns text for a shell script to check its exp log file for errors
#
sub check_exp_log
{
  my ( $shell, $log ) = @_;

  return
"
cat $log

EXP=\` grep -c ^EXP- $log \`
ORA=\` grep -c ^ORA- $log \`

if [ \${ORA} -gt 0 -o \${EXP} -gt 0 ]
" .
check( @_ );
}

# sub check_imp_log
#
# returns text for a shell script to check its imp log file for errors
#
sub check_imp_log
{
  my ( $shell, $log ) = @_;

  # Check log for errors, but ignore:
  #   IMP-00057 -- Not all partitions imported (we didn't export them all)
  #   IMP-00041 -- Store PL/SQL compilation errors (not our fault)
  return
"
cat $log

IMP=\` grep -v ^IMP-00057 $log | \\
      grep -v ^IMP-00041 | \\
      grep -c ^IMP- \`
ORA=\` grep -c ^ORA- $log \`

if [ \${ORA} -gt 0 -o \${IMP} -gt 0 ]
" .
check( @_ );
}

# sub check_sql_log
#
# returns text for a shell script to check its SQL spool file for errors
#
sub check_sql_log
{
  my ( $shell, $log ) = @_;

  return
"
ORA=\` grep -c ^ORA- $log \`

if [ \${ORA} -gt 0 ]
" .
check( @_ );

}

# sub connect_to_oracle
#
# Requires both "user" and "password", or neither.  If "user" is supplied
# but not "password", will prompt for a "password".  On Unix systems, a
# system call to "stty" is made before- and after-hand to control echoing
# of keystrokes.  [How do we do this on Windows?]
#
sub connect_to_oracle
{
  if ( $args{ user } and not $args{ password } )
  {
    print "Enter password: ";
    eval{ system("stty -echo" ); };
    chomp( $args{ password } = <STDIN> );
    print "\n";
    eval{ system( "stty echo" ); };
  }

  $args{ sid }      = "" unless $args{ sid };
  $args{ user }     = "" unless $args{ user };
  $args{ password } = "" unless $args{ password };

  $dbh = DBI->connect(
                       "dbi:Oracle:$args{ sid }",
                       "$args{ user }",
                       "$args{ password }",
                       {
                         PrintError => 0,
                         RaiseError => 1,
                       }
                     );

  # $dbh->do( "alter session set sql_trace = true" );

  DDL::Oracle->configure(
                          dbh    => $dbh,
                          view   => 'DBA',
                          schema => 1,
                          resize => $args{ resize } || 1,
                        );
}

# sub create_shell
#
# Opens, writes $text, closes the named shell script
#
sub create_shell
{
  my ( $script, $text ) = @_;

  print "Shell #$i is $script\n";
  open SHELL, ">$script"     or die "Can't open $script: $!\n";
  write_header( \*SHELL, $script, '# ' );
  print SHELL $text . "#  --- END OF FILE ---\n\n";
  close SHELL                  or die "Can't close $script: $!\n";
}

# sub drop_perf_temps
#
# Drops the temporary tables created to boost performance
#
sub drop_perf_temps
{
  foreach my $table ( @perf_tables )
  {
    $stmt =
     "
      SELECT
             'Yo!'
      FROM
             user_synonyms
      WHERE
             synonym_name = UPPER( ? )
     ";

    $sth = $dbh->prepare( $stmt );
    $sth->execute( $table );
    my $present = $sth->fetchrow_array;
    $dbh->do( "DROP SYNONYM $table" )    if $present;

    if ( $table =~ /^DBA/ )
    {
      $stmt =
       "
        SELECT
               'Present, sir!'
        FROM
               user_tables
        WHERE
               table_name = UPPER( ? )
       ";

      $sth = $dbh->prepare( $stmt );
      $sth->execute( "$prefix$table" );
      my $present = $sth->fetchrow_array;

      if ( $present )
      {
        $dbh->do( "TRUNCATE TABLE $prefix$table" );
        $dbh->do( "DROP     TABLE $prefix$table" );
      }
    }
    else
    {
      $stmt =
       "
        SELECT
               'Present, sir!'
        FROM
               user_tables
        WHERE
               table_name = ?
       ";

      $sth = $dbh->prepare( $stmt );
      $sth->execute( $table );
      my $present = $sth->fetchrow_array;

      if ( $present )
      {
        $dbh->do( "TRUNCATE TABLE $table" );
        $dbh->do( "DROP     TABLE $table" );
      }
    }
  }
}

# sub escaped_dollar_signs
#
# Routines dealing with the Temp tables, indexes and constraints must
# substitute generated names for the names of real objects returned by
# DDL::Oracle.  However, Oracle allows dollar signs ('$') within names
# for database objects.  This causes problems with the s/// operator,
# since it sees the '$' as a meta character, causing the substitution
# to fail.
#
# This little subroutine inserts a '\' in front of each '$', which
# effectively escapes it for the s/// operator.
#
sub escaped_dollar_signs
{
  my ( $str ) = @_;

  my $pos = 0;

  until ( $pos == -1 )
  {
    $pos = index( $str, '$', $pos );
    if ( $pos > -1 )
    {
      substr( $str, $pos, 0 ) = qq#\\#;
      $pos += 2;
    }
  }

  return $str;
}

# sub export_par_text
#
# Returns the text for the parfile of an export
#
sub export_par_text
{
  my ( $log, $table_aref ) = @_;

  my $text = "log          = $log\n" .
             "file         = $pipefile\n" .
             "rows         = y\n" .
             "grants       = y\n";

  # My linux Oracle 8.1.6 has a bug, so
  $text   .= "direct       = y\n"    unless $OSNAME eq 'linux';

  $text   .= "buffer       = 65535\n" .
             "indexes      = n\n" .
             "compress     = n\n" .
             "triggers     = y\n" .
             "statistics   = none\n" .
             "constraints  = n\n" .
             "recordlength = 65535\n" .
             "tables       = (\n" .
             "                   " .
             join ( "\n                 , ", @$table_aref ) .
             "\n               )\n\n";

  return $text
}

# sub get_args
#
# Uses supplied module Getopt::Long to place command line options into the
# hash %args.  Ensures that at least the mandatory argument --tablespace
# was supplied.  Also verifies directory arguments and connects to Oracle.
#
sub get_args
{
  #
  # Get options from command line and store in %args
  #
  GetOptions(
              \%args,
              "alttablespace:s",
              "expdir:s",
              "logdir:s",
              "password:s",
              "prefix:s",
              "sid:s",
              "resize:s",
              "sqldir:s",
              "tablespace:s",
              "user:s",
            );

  #
  # If there is anything left in @ARGV, we have a problem
  #
  die "\n***Error:  unrecognized argument",
      ( @ARGV == 1 ? ":  " : "s:  " ),
      ( join " ",@ARGV ),
      "\n$0 aborted,\n\n" ,
    if @ARGV;

  #
  # Validate arguments (maybe they type as badly as we do!
  #

  $tblsp = uc( $args{ tablespace } ) or
  die "\n***Error:  You must specify --tablespace=<NAME>\n",
      "\n$0 aborted,\n\n";

  $sqldir = ( $args{ sqldir } eq "." ) ? cwd : $args{ sqldir };
  die "\n***Error:  sqldir '$sqldir', is not a Directory\n",
      "\n$0 aborted,\n\n"
    unless -d $sqldir;

  die "\n***Error:  sqldir '$sqldir', is not a writeable Directory\n",
      "\n$0 aborted,\n\n"
    unless -w $sqldir;

  $logdir = ( $args{ logdir } eq "." ) ? cwd : $args{ logdir };
  die "\n***Error:  logdir '$logdir', is not a Directory\n",
      "\n$0 aborted,\n\n"
    unless -d $logdir;

  die "\n***Error:  logdir '$logdir', is not a writeable Directory\n",
      "\n$0 aborted,\n\n"
    unless -w $logdir;

  $expdir = ( $args{ expdir } eq "." ) ? cwd : $args{ expdir };
  die "\n***Error:  expdir '$expdir', is not a Directory\n",
      "\n$0 aborted,\n\n"
    unless -d $expdir;

  die "\n***Error:  sqldir '$expdir', is not a writeable Directory\n",
      "\n$0 aborted,\n\n"
    unless -w $expdir;

  $prefix = $args{ prefix };

  $add_ndx_log     = "$logdir/$prefix${tblsp}_add_ndx.log";
  $add_tbl_log     = "$logdir/$prefix${tblsp}_add_tbl.log";
  $add_temp_log    = "$logdir/$prefix${tblsp}_add_temp.log";
  $drop_all_log    = "$logdir/$prefix${tblsp}_drop_all.log";
  $drop_temp_log   = "$logdir/$prefix${tblsp}_drop_temp.log";
  $exp_log         = "$logdir/$prefix${tblsp}_exp.log";
  $imp_log         = "$logdir/$prefix${tblsp}_imp.log";
  $prttn_exp_log   = "$logdir/$prefix${tblsp}_prttn_exp.log";
  $prttn_imp_log   = "$logdir/$prefix${tblsp}_prttn_imp.log";

  push @logfiles, (
                    $add_ndx_log,
                    $add_tbl_log,
                    $add_temp_log,
                    $drop_all_log,
                    $drop_temp_log,
                    $exp_log,
                    $imp_log,
                    $prttn_exp_log,
                    $prttn_imp_log,
                  );

  validate_log_names( \@logfiles );

  $alttblsp = uc( $args{ alttablespace } );

  connect_to_oracle();      # Will fail unless sid, user, password are OK

  print "Initializing private copies of some dictionary views...\n\n";

  initialize_perf_temps();

  # Confirm the tablespace exists
  $stmt =
      "
       SELECT
              tablespace_name
       FROM
              dba_tablespaces  t
       WHERE
                  tablespace_name   = '$tblsp'
              AND status            = 'ONLINE'
              AND contents         <> 'TEMPORARY'
              AND extent_management = 'DICTIONARY'
       MINUS
       SELECT
              tablespace_name
       FROM
              dba_segments
       WHERE
              segment_type = 'ROLLBACK'
      ";

  $sth = $dbh->prepare( $stmt );
  $sth->execute;
  $row = $sth->fetchrow_array;

  die "\n***Error:  Tablespace \U$tblsp",
      " does not exist\n",
      "           or is not ONLINE\n",
      "           or is managed LOCALLY\n",
      "           or is a TEMPORARY tablespace\n",
      "           or contains ROLLBACK segments.\n\n"
    unless $row;

  # First row returned is valid tablespace, and is $alttblsp.
  # Since we know $tblsp is good, we're guaranteed at least one row.
  $stmt =
      "
       (
         SELECT
                tablespace_name
         FROM
                dba_tablespaces
         WHERE
                    tablespace_name   = '$alttblsp'
                AND status            = 'ONLINE'
                AND contents         <> 'TEMPORARY'
                AND extent_management = 'DICTIONARY'
         MINUS
         SELECT
                tablespace_name
         FROM
                dba_segments
         WHERE
                segment_type = 'ROLLBACK'
       )
       UNION ALL
       (
         SELECT
                tablespace_name
         FROM
                dba_tablespaces
         WHERE
                    tablespace_name   = 'USERS'
                AND status            = 'ONLINE'
                AND contents         <> 'TEMPORARY'
                AND extent_management = 'DICTIONARY'
         MINUS
         SELECT
                tablespace_name
         FROM
                dba_segments
         WHERE
                segment_type = 'ROLLBACK'
       )
       UNION ALL
       (
         SELECT
                '$tblsp'
         FROM
                dual
       )
      ";

  $sth = $dbh->prepare( $stmt );
  $sth->execute;
  $aref = $sth->fetchall_arrayref;

  $alttblsp = ( shift @$aref )->[0];

  my ( undef,undef,undef,$day,$month,$year,undef,undef,undef ) = localtime;
  $date = $year + 1900 . $month + 1 . $day;
}

# sub group_header
#
# Returns a Remark to identify the ensuing DDL statements
#
sub group_header
{
  my ( $nbr ) = @_;

  return 'REM ' . '#' x 60 . "\n" .
         "REM\n" .
         "REM                      Statement Group $nbr\n" .
         "REM\n" .
         'REM ' . '#' x 60 . "\n\n";
}

# sub import_par_text
#
# Returns the text for the parfile of an import
#
sub import_par_text
{
  my ( $log, $table_aref ) = @_;

  return            "log          = $log\n" .
                    "file         = $pipefile\n" .
                    "rows         = y\n" .
                    "commit       = y\n" .
                    "ignore       = y\n" .
                    "buffer       = 65535\n" .
                    "analyze      = n\n" .
                    "recordlength = 65535\n" .
                    "full         = y\n\n" .
                    "#tables       = (\n" .
                    "#                   " .
                    join ( "\n#                 , ", @$table_aref ) .
                    "\n#               )\n\n";
}

# sub index_and_exchange
#
# Generate the DDL to:
#
# 1.  Create an index on named temp table equal to every LOCAL index on the
#     named partitioned table.
# 2.  Create a PK for the temp table equal to the PK of the partitioned table,
#     if any.
# 3.  Exchange the temp table with the named partition.
#
sub index_and_exchange
{
  my (
       $temp,
       $owner,
       $table,
       $partition,
       $type,
       $partitioning_type,
       $analyzed
     ) = @_;

  my $sql;
  my $text;

  # Get partitioned, local indexes
  $stmt =
      "
       SELECT DISTINCT
              index_name
       FROM
              dba_indexes
       WHERE
                  owner      = ?
              AND table_name = ?
       MINUS
       SELECT                     -- Ignore GLOBAL indexes
              index_name
       FROM
              dba_part_indexes
       WHERE
                  owner      = ?
              AND table_name = ?
              AND locality   = 'GLOBAL'
       MINUS
       SELECT                     -- Ignore non-partitioned indexes
              segment_name
       FROM
              dba_segments
       WHERE
              segment_type = 'INDEX'
       ORDER
          BY
              1
      ";

  $sth = $dbh->prepare( $stmt );
  $sth->execute( $owner, $table, $owner, $table );
  $aref = $sth->fetchall_arrayref;

  foreach $row ( @$aref )
  {
    my $index = @$row->[0];

    $obj = DDL::Oracle->new(
                             type => 'exchange index',
                             list => [
                                       [
                                         "$owner",
                                         "$index:$partition",
                                       ]
                                     ],
                           );
    my $sql = $obj->create;
    # Remove REM lines created by DDL::Oracle
    $sql =  ( join "\n", grep !/^REM/, split /\n/, $sql ) . "\n\n";

    my $indx =  "${tblsp}_${date}_" . unique_nbr();

    # Change the CREATE INDEX statement
    # to use the Temp Index and Table names
    my $ownr = escaped_dollar_signs( $owner );
    my $tabl = escaped_dollar_signs( $table );
    my $indr = escaped_dollar_signs( $index );
    $sql     =~ s|\L$ownr.$indr|\L$owner.$indx|g;
    $sql     =~ s|\L$ownr.$tabl|\L$owner.$temp|g;

    $text .= $sql;
  }

  $stmt =
      "
       SELECT
              constraint_name
       FROM
              THE_CONSTRAINTS
       WHERE
                  owner           = ?
              AND table_name      = ?
              AND constraint_type = 'P'
      ";

  $sth = $dbh->prepare( $stmt );
  $sth->execute( $owner, $table );
  my @row = $sth->fetchrow_array;

  if ( @row )
  {
    my ( $constraint ) = @row;

    $obj = DDL::Oracle->new(
                             type => 'constraint',
                             list => [
                                       [
                                         "$owner",
                                         "$constraint",
                                       ]
                                     ],
                           );
    my $sql = $obj->create;
    # Remove REM lines created by DDL::Oracle
    $sql =  ( join "\n", grep !/^REM/, split /\n/, $sql ) . "\n\n";

    my $cons =  "${tblsp}_${date}_" . unique_nbr();

    # Change the ALTER TABLE ADD CONSTRAINT statement
    # to use the Temp Constraint and Table names
    my $ownr = escaped_dollar_signs( $owner );
    my $tabl = escaped_dollar_signs( $table );
    my $conr = escaped_dollar_signs( $constraint );
    $sql     =~ s|\L$ownr.$tabl|\L$owner.$temp|g;
    $sql     =~ s|\L$conr|\L$cons|g;

    $text .= $sql;
  }

  if ( $analyzed eq 'YES' )
  {
    $text .= "PROMPT " .
             "ANALYZE TABLE \L$owner.$temp\n\n" .
             "ANALYZE TABLE \L$owner.$temp \UESTIMATE STATISTICS\n" .
             "   FOR TABLE\n" .
             "   FOR ALL INDEXED COLUMNS ;\n\n";
  }

  $text .= "PROMPT " .
           "ALTER TABLE \L$owner.$table \UEXCHANGE $type \L$partition\n\n" .
           "ALTER TABLE \L$owner.$table\n" .
           "   \UEXCHANGE $type \L$partition \UWITH TABLE \L$owner.$temp\n" .
           "   INCLUDING INDEXES\n".
           "   WITHOUT VALIDATION ;\n\n";

  return $text;
}

# sub initialize_perf_temps
#
sub initialize_perf_temps
{
  # Drop the Performance enhancing tables -- they shouldn't be here,
  # but who knows, maybe we crashed last time (how rude!)

  drop_perf_temps();

  # Some Dictionary views are queried repeatedly by us (defrag.pl) as well
  # as by DDL::Oracle.  They are often complex views, taking as much as 3
  # to 10 seconds for each query on a large database (e.g., 50,000 segments).
  # Let's get our own, more efficient copy of this data and avoid this
  # overhead

  TABLE:
    foreach my $table ( @perf_tables )
    {
      next TABLE unless $table =~ /^DBA/;

      $dbh->do
      ( "
         CREATE GLOBAL TEMPORARY TABLE $prefix$table
         ON COMMIT PRESERVE ROWS
         AS
         SELECT
                *
         FROM
                sys.$table
        "
      );
      $dbh->do( "CREATE SYNONYM $table FOR $prefix$table" );
    }
}

# sub initialize_queries
#
# Initializes the driving queries used to retrieve object names involved in
# the defrag.  Because these are UNIONed and MINUSed, at times, store the
# the results in in-memory temporary tables for efficiency reasons.
#
sub initialize_queries
{
  # This query produces a list of THE CONSTRAINTS, sans search_condition
  # which is needed for creating Check Constraints
  $stmt =
      "
       CREATE GLOBAL TEMPORARY TABLE the_constraints
       ON COMMIT PRESERVE ROWS
       AS
       SELECT
              owner
            , constraint_name
            , constraint_type
            , table_name
            , r_owner
            , r_constraint_name
       FROM
              dba_constraints
      ";

  $dbh->do( $stmt );

  # This query produces a list of THE PARTITIONS, which are the partitions
  # in THE TABLESPACE belonging to tables which have at least one partition
  # in some other tablespace.  These will be the target of ALTER TABLE
  # EXCHANGE [SUB]PARTITION statements with "temp" tables.
  #
  $stmt =
      "
       CREATE GLOBAL TEMPORARY TABLE the_partitions
       ON COMMIT PRESERVE ROWS
       AS
       SELECT
              s.owner
            , s.segment_name
            , s.partition_name
            , SUBSTR(s.segment_type,7)                 AS segment_type
            , p.partitioning_type                      AS partitioning_type
            , DECODE(
                      s.segment_type
                     ,'TABLE PARTITION'   ,DECODE(
                                                   a.last_analyzed
                                                  ,null,'NO'
                                                  ,'YES'
                                                 )
                     ,'TABLE SUBPARTITION',DECODE(
                                                   b.last_analyzed
                                                  ,null,'NO'
                                                  ,'YES'
                                                 )
                    )                                  AS analyzed
       FROM
              dba_segments          s
            , dba_part_tables       p
            , dba_tab_partitions    a
            , dba_tab_subpartitions b
       WHERE
                  p.table_name            = s.segment_name
              AND s.segment_type       LIKE 'TABLE%PARTITION'
              AND s.tablespace_name       = '$tblsp'
              AND a.table_name        (+) = s.segment_name
              AND b.table_name        (+) = s.segment_name
              AND a.partition_name    (+) = s.partition_name
              AND b.subpartition_name (+) = s.partition_name
              AND a.table_owner       (+) = s.owner
              AND b.table_owner       (+) = s.owner
              AND EXISTS (
                           SELECT
                                  null
                           FROM
                                  dba_segments
                           WHERE
                                      segment_type  LIKE 'TABLE%PARTITION'
                                  AND tablespace_name <> '$tblsp'
                                  AND owner            = s.owner
                                  AND segment_name     = s.segment_name
                         )
              AND (
                      s.owner
                    , s.segment_name
                  ) NOT IN (
                             SELECT
                                    owner
                                  , table_name
                             FROM
                                    dba_snapshots
                           )
      ";

  $dbh->do( $stmt );

  # This query produces a list of THE INDEXES (and their tables) -- those
  # non-partitioned indexes which reside in THE TABLESPACE, plus indexes
  # which have at least one partition in THE TABLESPACE.  These indexes are
  # on tables other than the tables of THE PARTITIONS but may be on THE
  # TABLES.
  #
  $stmt =
      "
       CREATE GLOBAL TEMPORARY TABLE the_indexes
       ON COMMIT PRESERVE ROWS
       AS
       SELECT
              owner
            , index_name
            , table_name
            , MAX(analyzed)        AS analyzed
       FROM
            (
              SELECT
                     owner
                   , index_name
                   , table_name
                   , DECODE(
                             last_analyzed
                            ,null,'NO'
                            ,'YES'
                           )                  AS analyzed
              FROM
                     dba_indexes
              WHERE
                         tablespace_name = '$tblsp'
                     AND index_type     <> 'IOT - TOP'
              UNION ALL
              SELECT
                     i.owner
                   , i.index_name
                   , i.table_name
                   , DECODE(
                             p.last_analyzed
                            ,null,'NO'
                            ,'YES'
                           )                  AS analyzed
              FROM
                     dba_indexes         i
                   , dba_ind_partitions  p
              WHERE
                         p.tablespace_name = '$tblsp'
                     AND i.owner           = p.index_owner
                     AND i.index_name      = p.index_name
                     AND i.index_type     <> 'IOT - TOP'
              UNION ALL
              SELECT
                     i.owner
                   , i.index_name
                   , i.table_name
                   , DECODE(
                             p.last_analyzed
                            ,null,'NO'
                            ,'YES'
                           )                  AS analyzed
              FROM
                     dba_indexes            i
                   , dba_ind_subpartitions  p
              WHERE
                         p.tablespace_name = '$tblsp'
                     AND i.owner           = p.index_owner
                     AND i.index_name      = p.index_name
                     AND i.index_type      <> 'IOT - TOP'
            )
       WHERE
             (
                 owner
               , table_name
             ) NOT IN (
                        SELECT
                               owner
                             , segment_name
                        FROM
                               THE_PARTITIONS
                      )
       GROUP
          BY
              owner
            , index_name
            , table_name
      ";

  $dbh->do( $stmt );

  # This query produces a list of THE IOTs -- non-partition index organized
  # tables which reside in THE TABLESPACE or partitioned index organized
  # tables which have at least one partition in THE TABLESPACE.
  #
  $stmt =
      "
       CREATE GLOBAL TEMPORARY TABLE the_IOTs
       ON COMMIT PRESERVE ROWS
       AS
       SELECT
              owner
            , table_name
            , MAX(analyzed)        AS analyzed
       FROM
            (
              SELECT
                     owner
                   , table_name
                   , DECODE(
                             last_analyzed
                            ,null,'NO'
                            ,'YES'
                           )                  AS analyzed
              FROM
                     dba_indexes
              WHERE
                         tablespace_name = '$tblsp'
                     AND index_type      = 'IOT - TOP'
              UNION ALL
              SELECT
                     i.owner
                   , i.table_name
                   , DECODE(
                             p.last_analyzed
                            ,null,'NO'
                            ,'YES'
                           )                  AS analyzed
              FROM
                     dba_indexes         i
                   , dba_ind_partitions  p
              WHERE
                         p.tablespace_name = '$tblsp'
                     AND i.index_type      = 'IOT - TOP'
                     AND i.owner           = p.index_owner
                     AND i.table_name      = p.index_name
              UNION ALL
              SELECT
                     i.owner
                   , i.table_name
                   , DECODE(
                             p.last_analyzed
                            ,null,'NO'
                            ,'YES'
                           )                  AS analyzed
              FROM
                     dba_indexes            i
                   , dba_ind_subpartitions  p
              WHERE
                         p.tablespace_name = '$tblsp'
                     AND i.index_type      = 'IOT - TOP'
                     AND i.owner           = p.index_owner
                     AND i.table_name      = p.index_name
            )
       GROUP
          BY
              owner
            , table_name
      ";

  $dbh->do( $stmt );

  # This query produces a list of THE TABLES -- non-partitioned tables which
  # reside in THE TABLESPACE or partitioned tables which have at every
  # partition in THE TABLESPACE.
  #
  $stmt =
      "
       CREATE GLOBAL TEMPORARY TABLE the_tables
       ON COMMIT PRESERVE ROWS
       AS
       SELECT
              owner
            , table_name
            , MAX(analyzed)        AS analyzed
       FROM
            (
              SELECT
                     owner
                   , table_name
                   , DECODE(
                             last_analyzed
                            ,null,'NO'
                            ,'YES'
                           )                  AS analyzed
              FROM
                     dba_tables
              WHERE
                     tablespace_name   = '$tblsp'
              UNION ALL
              SELECT
                     table_owner
                   , table_name
                   , DECODE(
                             last_analyzed
                            ,null,'NO'
                            ,'YES'
                           )                  AS analyzed
              FROM
                     dba_tab_partitions  t
              WHERE
                         tablespace_name   = '$tblsp'
                     AND NOT EXISTS (
                                      SELECT
                                             null
                                      FROM
                                             dba_tab_partitions
                                      WHERE
                                                 table_owner = t.table_owner
                                             AND table_name  = t.table_name
                                             AND tablespace_name <> '$tblsp'
                                      UNION ALL
                                      SELECT
                                             null
                                      FROM
                                             dba_tab_subpartitions
                                      WHERE
                                                 table_owner = t.table_owner
                                             AND table_name  = t.table_name
                                             AND tablespace_name <> '$tblsp'
                                    )
              UNION ALL
              SELECT
                     table_owner
                   , table_name
                   , DECODE(
                             last_analyzed
                            ,null,'NO'
                            ,'YES'
                           )                  AS analyzed
              FROM
                     dba_tab_subpartitions  t
              WHERE
                         tablespace_name   = '$tblsp'
                     AND NOT EXISTS (
                                      SELECT
                                             null
                                      FROM
                                             dba_tab_subpartitions
                                      WHERE
                                                 table_owner = t.table_owner
                                             AND table_name  = t.table_name
                                             AND tablespace_name <> '$tblsp'
                                    )
              -- Ignore Snapshots/Materialized Views.
              -- Yeah, it's a cop out.
              MINUS
              SELECT
                     owner
                   , table_name
                   , 'YES'                    AS analyzed
              FROM
                     dba_snapshots
              MINUS
              SELECT
                     owner
                   , table_name
                   , 'NO'                     AS analyzed
              FROM
                     dba_snapshots
            )
       GROUP
          BY
              owner
            , table_name
      ";

  $dbh->do( $stmt );
}

# sub move
#
# Formats an ALTER TABLE MOVE [SUB]PARTITION statement
#
sub move
{
  my (
       $owner,
       $table,
       $partition,
       $type,
       $part_type,
       $analyzed,
       $tblsp,
     ) = @_;

  my $sql = "PROMPT " .
            "ALTER TABLE \L$owner.$table \UMOVE $type \L$partition\n\n" .
            "ALTER TABLE \L$owner.$table \UMOVE $type \L$partition\n" .
            "TABLESPACE \L$tblsp\n";

  # Can't specify INITIAL/NEXT on HASH partitions,
  # and all subpartitions are currently HASH
  if ( $type eq 'PARTITION' and $part_type eq 'RANGE' )
  {
    $sql .= "STORAGE\n" .
            "(\n" .
            "  INITIAL  2K\n" .
            "  NEXT     2K\n" .
            ") ";
  }

  return $sql .= ";\n\n";
}

# sub print_help
#
# Displays a description of each argument.
#
sub print_help
{
  print "
  Usage:  defrag.pl [OPTION] [OPTION]...

  ?, -?, -h, --help   Prints this help.

  --tablespace=TABLESPACE

           Drop/recreate all objects in the named tablespace -- tables,
           table partitions, non-partitioned indexes and indexes which
           have even one partition in the named tablespace.

           This argument is REQUIRED.

  --alttablespace=TABLESPACE

           If table partition(s) is(are) part of the defrag, a
           substitute, placeholder partition is created in this
           tablespace.  If not given, tablespace USERS will be used if
           present, otherwise the named tablespace.  If the argument
           is not given, and if there are partitioned tables in the
           named tablespace, and if there is not a USERS tablespace,
           the placeholder partitions will probably prevent a complete
           coalesce of the named tablesapace.  This argument is highly
           recommended.

  --expdir=PATH *

           Directory to place the import/export .par files.  Defaults to
           environment variable DBA_EXP, or to the current directory.

  --logdir=PATH *

           Directory to place the import/export .log files, as well
           as the SPOOLed .log files created by SQL*Plus.  Defaults to
           environment variable DBA_LOG, or to the current directory.

  --password=PASSWORD

           User's password.  Not required if user is authenticated
           externally.  Respresents a security risk on Unix systems.

           If USER is given and PASSWORD is not, program will prompt
           for PASSWORD.  This would be preferable to entering the
           password on the command line, since the password will then
           not be visible in a 'ps' command.

  --prefix=STRING *

           The leading portion of all filenames.  Defaults to 'defrag_',
           and may be '' (in which case filenames will begin with the
           name of the tablespace).

  --sid=SID *

           The SID or service used to connect to Oracle.  If omitted,
           the connection will be to the instance identified in
           environment variable ORACLE_SID.

  --resize=STRING *

           In the CREATE statement, objects are given INITIAL and NEXT
           extent sizes, appropriate for objects having the number of
           blocks used.  This is a colon delimited string consisting
           of n sets of LIMIT:INITIAL:NEXT.  LIMIT is expressed in
           Database Blocks.  The highest LIMIT may contain the string
           'UNLIMITED', and in any event will be forced to be so by
           DDL::Oracle.

  --sqldir=PATH *

           Directory to place the SQL (.sql) files.  Defaults to
           environment variable DBA_SQL, or to the current directory.

  --user=USERNAME

           Connects to Oracle as this user.  Defaults to operating
           system username.

  *  Items marked with '*' are saved in a file named .defragrc,
     stored in the user's HOME directory.  If omitted in subsequent
     usages of defrag.pl, these entries will be reused unless a
     new entry is assigned at that time.

  ";

  $text = "
  Program 'defrag.pl' uses 5 main SQL statements to retrieve record sets which
  form the basis of generated DDL.  They are sometimes UNIONed, sometimes
  MINUSed, etc., to refine the record sets.  The queries are:

  THE TABLESPACE -- the Tablspace named by the '--tablespace=<name>' argument.

  THE CONSTRAINTS -- provides a substitute for DBA_CONSTRAINTS, sans column
  SEARCH_CONDITION.

  THE TABLES -- provides a list of Owner/Table_name's which fully reside in
  THE TABLESPACE.  These are non-partitioned tables plus partitioned tables
  where every partition and subpartition reside in THE TABLESPACE.  This list
  excludes IOT tables.

  THE IOTS -- provides a list of Owner/Table_name's which fully or partially
  reside in THE TABLESPACE.  In other words, if a partitioned IOT table has
  even one partition in THE TABLESPACE, it is included in this list.  Reasons
  these are in  a separate list from THE TABLES include the fact that their
  Primary Key is part of the CREATE TABLE syntax, and there are never other
  indexes on them,

  THE INDEXES -- provides a list of Owner/Index_name/Table_name's for indexes
  not belonging to THE TABLES but which fully or partially reside in THE
  TABLESPACE.  In other words, a partitioned index with even one partition in
  THE TABLESPACE is included in this list.

  The data in THE TABLES and THE IOTS will be exported, after which members of
  all 3 of the lists will be dropped before THE TABLESPACE is coalesced into
  as few as 1 extent per datafile.

  THE PARTITIONS -- provides Owner/Table_name/Partition_name/Segment_type's
  for all partitions and subpartitions not belonging to THE TABLES nor to THE
  IOTS but which are located in THE TABLESPACE.  If any of these exist, the
  first step will be to perform a 'safety' export of their data directly from
  THE PARTITIONS.  Under normal circumstances, this export is not used.
  Rather, for each partition a corresponding 'temp' table is built matching
  the partition in structure, indexes and Primary Key.  The temp table is then
  EXCHANGED with the partition; this results in the temp table holding the
  data and the partition becoming empty.  The empty partition is moved to the
  alternate tablespace before the coalescing takes place.  The temp table is
  then treated like a member of THE TABLES (i.e., exported, dropped,
  recreated, indexed, imported, etc.).  After the temp table has its data
  imported, it is again EXCHANGED with its original partition, and thus the
  data once again becomes part of the table in its new, properly sized
  segment.

  Note that nothing is done with indexes on the tables of THE PARTITIONS.  In
  the event that such an index or a partition thereof happens to reside in THE
  TABLESPACE, it will still be there after all other objects have been dropped
  or moved eleehwhere.  Likewise, unless an alternate tablespace other than
  THE TABLESPACE is given (or if the named alternate tablespace does not
  exist), then the empty partition segments will also remain in THE TABLESPACE.
  If either of these conditions occurs, the THE TABLESPACE will not be
  completely empty when it is coalesced.  This is not necessarily a big
  problem, it is just not as clean as when THE TABLESPACE becomes completely
  empty before it is coalesced.

  The following descriptions of the 'Statement Groups' show the sequence of
  statments used to defragment THE TABLESPACE.  These DDL statements are in
  3 to 5 files.  Shell scripts are provided which perform the statements in
  the correct sequence, intermingled with the exports and imports.  The user
  should check the execution of each shell script for errors before continuing
  with the next step.  Within the SQL files, each group of statements is
  delineated by a header record which refers to a 'Statement Group Number'.
  These groups are defined below.

  EXPORT the data from THE PARTITIONS. (If all goes well, we won't use this.)

   1.  For each member of THE PARTITIONS:
         a.  Create a Temp table.
         b.  Add appropriate indexes.
         c.  Add a PK, if any.
         d.  EXCHANGE the Temp table with the partition.
         e.  MOVE the [now empty] Temp table to the alternate tablespace.

  EXPORT the data from THE TABLES, THE IOTS and the Temp tables.

   2.  DROP the Temp tables created in Group #1.

   3.  DROP all Foreign Keys referencing THE TABLES, THE IOTS or the tables
       of THE INDEXES.

   4.  DROP members of THE TABLES and THE IOTS.  Note: this DROPs all
       constrints on these tables.

   5.  DROP Primary Keys, Unique Constraints and Check Constraints on the
       tables of THE INDEXES.

   6.  DROP members of THE INDEXES unless they enforce a Primay Key or Unique
       Constraint of the same name -- those that do disappeared in Group #5.
       Note: this will generate DROP INDEX statements for PK/UK's if the
       Constraint name differs from the Index name (e.g., system generated
       names).  It won't cause any harm, but it will show an error in the log
       file spooled in SQL*Plus; these should be ignored.  Maybe we'll fix
       this someday.

   7.  CREATE the Temp tables.

   8.  CREATE members of THE TABLES and THE IOTS.

  IMPORT the data for THE TABLES, THE IOTS and the Temp tables.

   9.  CREATE indexes and PK's on the Temp tables.  EXCHANGE them with their
       corresponding partition, and DROP the now empty Temp tables.

  10.  CREATE indexes on THE TABLES, plus THE INDEXES themselves.

  11.  CREATE all Constraints on THE TABLES.

  12.  CREATE Check Cosntraints on THE IOTS.

  13.  CREATE Foreign Keys referencing THE TABLES, THE IOTS or the tables
       of THE INDEXES.

  14.  REBUILD non-partitioned or Global partitioned indexes on THE PARTITIONS
       (these were marked UNUSABLE during the partition EXCHANGE).

  ONLY IF PROBLEMS OCCURED DURING EXECUTION OF GROUP #1:

  15.  DROP the Temp tables.

  IMPORT the data for THE PARTITIONS.

  ";

  write_file( "./README.defrag", $text, '' );

  print "
  Also, see the 'README.defrag' which was just written in this directory
  for information about the DDL statements generated and their sequence.
  ";

  return;
}

# sub set_defaults
#
# If file HOME/.defragrc exists, reads its contents into hash %args.
# Otherwise, fill the hash with arbitrary defaults.
#
sub set_defaults
{
  if ( -e "$home/.defragrc" )
  {
    # We've been here before -- set up per .defragrc
    open RC, "<$home/.defragrc"    or die "Can't open $home/.defragrc:  $!\n";
    while ( <RC> )
    {
      chomp;                       # no newline
      s/#.*//;                     # no comments
      s/^\s+//;                    # no leading white space
      s/\s+$//;                    # no trailing white space
      next unless length;          # anything left? (or was blank)
      my ( $key, $value ) = split( /\s*=\s*/, $_, 2 );
      $args{ $key } = $value;
    }
    close RC                       or die "Can't close $home/.defragrc:  $!\n";

    # Just in case they farkled the .defragrc file
    $args{ expdir } = '.'       unless $args{ expdir };
    $args{ sqldir } = '.'       unless $args{ sqldir };
    $args{ logdir } = '.'       unless $args{ logdir };
    $args{ prefix } = 'defrag_' unless $args{ prefix };
  }
  else
  {
    # First time for this user
    $args{ expdir } = $ENV{ DBA_EXP }    || ".";
    $args{ sqldir } = $ENV{ DBA_SQL }    || ".";
    $args{ logdir } = $ENV{ DBA_LOG }    || $ENV{ LOGDIR } || ".";
    $args{ prefix } = "defrag_";
  }
  Getopt::Long::Configure( 'passthrough' );
}

# sub trunc
#
# Formats a TRUNCATE statement for the supplied [sub]partition
#
sub trunc
{
  my ( $owner, $table, $partition, $type ) = @_;

  return  "PROMPT " .
          "ALTER TABLE \L$owner.$table \UTRUNCATE $type \L$partition  \n\n" .
          "ALTER TABLE \L$owner.$table \UTRUNCATE $type \L$partition ;\n\n";
}

# sub unique_nbr
#
# Generates a unique 6-digit number for use in Temp Table names
#
sub unique_nbr
{
  my $nbr;

  while( 1 )
  {
    $nbr = int( rand 900000 ) + 100000;
    $uniq{ $nbr }++;
    last unless $uniq{ $nbr } > 1;
  }

  return $nbr
}

# sub validate_log_names
#
# Ensures that log files are writeable.  These files are not actually
# OPENed during the program, so this check is not foolproof, but it
# might save a little time just in case the filename is unwriteable.
#
sub validate_log_names
{
  my ( $aref ) = @_;

  foreach my $file ( @$aref )
  {
    die "\n***Error:  Log file $file\n",
        "           is not writeable\n",
        "\n$0 aborted,\n\n"
      unless (
                  -e $file and -w $file
               or not -e $file
             );
  }
}

# sub write_file
#
# Opens, writes, closes a .sql or .par file
#
sub write_file
{
  my ( $filename, $text, $remark ) = @_;

  open FILE, ">$filename"     or die "Can't open $filename: $!\n";
  write_header( \*FILE, $filename, $remark );
  print FILE $text,
             "$remark --- END OF FILE ---\n\n";
  close FILE                  or die "Can't close $filename: $!\n";
}

# sub write_header
#
# Creates a 7-line header in the supplied file, marked as comments.
#
sub write_header
{
  my ( $fh, $filename, $remark ) = @_;

  print $fh "$remark $filename\n",
            "$remark \n",
            "$remark Created by $0\n",
            "$remark on ", scalar localtime,"\n\n\n\n";
}

# $Log: defrag.pl,v $
# Revision 1.18  2001/04/28 13:51:28  rvsutherland
# Fixed to work on Windows [I think].
#
# Revision 1.17  2001/01/27 16:23:25  rvsutherland
# Upgraded to handle tablespaces having no tables (only indexes).
#
# Revision 1.16  2001/01/14 16:47:55  rvsutherland
# Nominal changes for version 0.32
#
# Revision 1.15  2001/01/07 16:44:54  rvsutherland
# Changed 'WITHOUT' to 'without' in success message of scripts
#
# Revision 1.14  2001/01/01 22:43:21  rvsutherland
# Altered shell scripts to be completely self checking.
# Added driver shell script to call all other scripts, so that defragging
#    could take place in background while DBA eats pizza.
#
# Revision 1.13  2001/01/01 12:59:52  rvsutherland
# Fixed bug in export parfile.
#
# Revision 1.12  2000/12/31 12:51:59  rvsutherland
# Added ANALYZE TABLE/INDEX following Import, for previously analyzed objects
#
# Revision 1.11  2000/12/31 00:46:58  rvsutherland
# Before starting, verified that Log files were writiable.
# Modified queries in anticipation of adding ANALYZE TABLE statements
#
# Revision 1.10  2000/12/28 21:45:25  rvsutherland
# Upgraded to handle table names containing '$'.
# Corrected Statement Group 15 to MOVE the partitions back to THE TABLESPACE.
# Put all Log files in logdir (were going to sqldir -- go figure)
# Corrected NEXT size if object reached last tier (was null)
#
# Revision 1.9  2000/12/09 17:38:56  rvsutherland
# Additional tuning refinements.
# Minor cleanup of code.
#
# Revision 1.8  2000/12/06 00:43:45  rvsutherland
# Significant performance improvements.
# No, make that MAJOR gains (i.e., orders of magnitude for large databases).
# To wit:
#   Replaced convoluted Dictionary views with 8i Temporary Tables
#   Widely (but not entirely) switched to bind variables (was interpolated,
#     causing reparsing in most cases).
# Also fixed error on REBUILD of Global and non-partitioned indexes.
#
# Revision 1.7  2000/12/02 14:06:20  rvsutherland
# Completed 'exchange' method for handling partitions,
# including REBUILD of UNUSABLE indexes.
# Removed 'resize' method for handling partitions.
#
# Revision 1.6  2000/11/26 20:10:54  rvsutherland
# Added 'exchange' method for handling partitions.  Will probably
# remove the 'resize' method next update.
#
# Revision 1.5  2000/11/24 18:36:00  rvsutherland
# Restructured file writes
# Revamped 'resize' method for handling partitions
#
# Revision 1.4  2000/11/19 20:08:58  rvsutherland
# Added 'resize' partitions option.
# Restructured file creation.
# Added shell scripts to simplify executing generated files.
# Modified selection of IOT tables (now handled same as indexes)
# Added validation of input arguments -- meaning we now check for
# hanging chad and pregnant votes  ;-)
#
# Revision 1.3  2000/11/17 21:35:53  rvsutherland
# Commented out Direct Path export -- Import has a bug (at least on Linux)
#
# Revision 1.2  2000/11/16 09:14:38  rvsutherland
# Major restructure to take advantage of DDL::Oracle.pm
#

__END__

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

=head1 NAME

defrag.pl -- Creates SQL*Plus command files to defragment a tablespace.

=head1 SYNOPSIS

[ ? | -? | -h | --help ]

--tablespace=TABLESPACE

[--alttablespace=TABLESPACE]

[--expdir=PATH]

[--logdir=PATH]

[--resize=STRING]

[--sqldir=PATH]

[--user=USERNAME]

[--password=PASSWORD]

[--prefix=PREFIX]

[--sid=SID]

[--resize=STRING]

Note:  descriptions of each of these arguments are provided via 'help',
which may be displayed by entering 'defrag.pl' without any arguments.

=head1 DESCRIPTION

Creates command files to defragment (reorganize) an entire Oracle
Tablespace.  Arguments are specified on the command line.

A "defrag" is usually performed to recapture the little fragments of
unused (and unusable) space that tend to accumulate in Oracle
tablespaces when objects are repeatedly created and dropped.. To fix
this, data is first exported.  Objects are then dropped and the
tablespace is "coalesced" into one large extent of available space.  The
objects are then recreated using either the default sizing algorithm or a
user supplied algorithm, and the data is imported.  Space utilized is then
contiguous, and the unused free space has been captured for use.

The steps in the process are:

    1.  Export all objects in the tablespace (tables, indexes, partitions).
    2.  Drop all objects.
    3.  Coalesce the tablespace.
    4.  Create all tables and partitions, resized appropriately.
    5.  Import the data into the new structures.
    6.  Recreate the indexes.
    7.  Restore all constraints.

=head1 TO DO

=head1 BUGS

=head1 FILES

The names and number of files output varies according to the Tablespace
specified and the options selected.  All .sql and .log files and shell
scripts produced are displayed on STDOUT during the execution of the program.

Also, see 'README.defrag', which will be created when Help is displayed (by
entering 'defrag.pl' without any arguments).

=head1 AUTHOR

 Richard V. Sutherland
 [e-mail]

=head1 COPYRIGHT

Copyright (c) 2000, 2001 Richard V. Sutherland.  All rights reserved.
This script is free software.  It may be used, redistributed, and/or
modified under the same terms as Perl itself.  See:

    http://www.perl.com/perl/misc/Artistic.html

=cut