Perl Script to get DDL from Indexfile

From: mohammed bhatti <mkb125_at_yahoo.com>
Date: Wed, 02 Jan 2002 17:01:56 -0800
Message-ID: <F001.003E58C8.20020102164025@fatcity.com>

Finally, to get the ALTER statements from the indexfile:

#!/usr/local/bin/perl -w

################
# Extract ALTER TABLE statements
# By: MKB Jan 02 2002
# Inputs: Location of log file and file output
# Outputs: Writes to file output
# Modification History:
#
################

use strict;
my ($infile, $outfile);
my $usage = "usage: <input file> <output file>\n"; $usage = $usage . "Example: /home/oracle/file.log /home/oracle/alter.log \n";

if ($#ARGV != 1) { die($usage) }
else { $infile = $ARGV[0];

       $outfile = $ARGV[1];
     };

open my $fh, $infile or die print "can't open"; open my $fh_out, ">$outfile" or die

my @strg;
my $i = 0;
my $long_strg = "";

while (<$fh>) {

   $_=~ s/^REM //;
   $_=~ s/\n//;
   @strg = split / /, $_;
   # print all elements in array
   # and make one long string
   for ($i = 0; $i <= $#strg; $i++) {

      if ($strg[$i] ne ";") {
         $long_strg = $long_strg . " " . $strg[$i];
      }
      else {
         if ($long_strg =~ m/ALTER TABLE/) {
            $long_strg =~ s/"//g;
            $long_strg =~ s/ALTER TABLE
((\w+).(\w+))/ALTER TABLE $1\n/;
            $long_strg =~ s/ADD CONSTRAINT
(\w+|\W+)/ADD CONSTRAINT $1\n/;
            $long_strg =~ s/PRIMARY KEY
(\w+|\W+)/PRIMARY KEY $1\n/;
            $long_strg =~ s/(\w+|\W+),/ $1,\n/g;
            $long_strg =~ s/INITRANS/\n INITRANS/g;
            $long_strg =~ s/MINEXTENTS/\n
MINEXTENTS/g;
            $long_strg =~ s/PCTINCREASE/\n
PCTINCREASE/g;
            $long_strg =~ s/PCTFREE (\d+) /\n PCTFREE
$1 /g;
            $long_strg =~ s/((LOGGING|NOLOGGING)
(LOCAL|GLOBAL))\(/$1\n\(/g;
            $long_strg =~ s/(TABLESPACE
(\w+|\W+))|(TABLESPACE (\w+\W+) LOB)/\n $1/g;
            $long_strg =~ s/ STORAGE\(/\n STORAGE
\(/g;
            print $fh_out $long_strg . ";\n\n";
        }
         $long_strg = "";
      }

}
}

close $fh;
close $fh_out;

Do You Yahoo!?
Send your FREE holiday greetings online! http://greetings.yahoo.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: mohammed bhatti
  INET: mkb125_at_yahoo.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Wed Jan 02 2002 - 19:01:56 CST

Perl Script to get DDL from Indexfile - Part3