view release on metacpan or  search on metacpan

defrag.pl  view on Meta::CPAN

#! /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.
#