pkg://DDL-Oracle-1.04.tar.gz:46377/
DDL-Oracle-1.04/defrag.pl
downloads
#! /usr/bin/perl -w
# $Id: defrag.pl,v 1.17 2001/01/27 16:23:25 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;
########################################################################
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',DE
