Re: Export whole schema except data from 1 table?

From: Mladen Gogala <gogala_at_sbcglobal.net>
Date: Wed, 21 Jun 2006 00:56:22 -0400
Message-Id: <1150865782l.5172l.1l@medo.noip.com>

On 06/20/2006 11:40:56 PM, Dennis Williams wrote:
> Michael,
>
> Time to learn the DBAs friend.
> select table_name||"," from user_tables
>
> Dennis Williams

Something like this might also be helpful:

#!/usr/bin/perl -w
use strict;
use DBI;
my ( $username, $password, $base, $notlike, $direct ); my ( $file, $buffsize, $pattern ) = ( "export.par", 10485760, "%" ); my $expfile = "expdat.dmp";

my $TAB     = qq(select table_name from user_tables 
                 where table_name like upper(:PATT));

use Getopt::Long;

# Parse command line options

my $stat = GetOptions( "u|username=s" => \$username,
                       "p|password=s" => \$password,
                       "d|db=s"       => \$base,
                       "f|file=s"     => \$file,
                       "e|expfile=s"  => \$expfile,
                       "b|buff=s"     => \$buffsize,
                       "t|patt=s"     => \$pattern,
                       "c|direct"     => \$direct,
                       "n|negate"     => \$notlike,
                       "h|help|?"     => \&usage

);
$base ||= $ENV{"TWO_TASK"};

die("Target database is unknown.\n") unless defined($base);
if ( !defined($username) || !defined($password) || !$stat ) { usage(); }
if ($notlike) {
    $TAB = qq(select table_name from user_tables 
              where table_name not like upper(:PATT));

}
my $dbh = db_connect( $username, $password, $base ); my $sth = $dbh->prepare($TAB);
$sth->bind_param( ":PATT", $pattern );
$sth->execute();
open( OUT, ">", $file ) or die "Cannot open $file for writing:$!\n";

print OUT "userid=$username/$password\@$base\n";
print OUT "file=$expfile\n";
print OUT "buffer=$buffsize\n";
print OUT "direct=y\n" if $direct;
my $out = "tables=(\n";

while ( my @row = $sth->fetchrow_array() ) {

    $out .= $row[0] . ",\n";
}
$out =~ s/,\n$/\)/m;
print OUT "$out\n";
close(OUT);

END {
    $dbh->disconnect() if defined $dbh;
}

sub db_connect {

    my ( $username, $passwd, $db ) = ( @_, $ENV{"TWO_TASK"} );     my $dbh = DBI->connect( "dbi:Oracle:$db", $username, $passwd )

        || die( $DBI::errstr . "\n" );

    $dbh->{AutoCommit}    = 0;
    $dbh->{RaiseError}    = 1;
    $dbh->{ora_check_sql} = 0;
    $dbh->{RowCacheSize}  = 16;

    return ($dbh);
}

sub usage {

    use File::Basename;
    my $nm = basename($0);
    print qq($nm -> write export parameter file

      USAGE:$nm -u=user -p=passwd -d=dtabase -f file
      OPTIONS:  -u oracle username 
                -p password for the above
                -d database to connect to
                -f file to create
                -e export file to put in the parameter file
                -n negate condition
                -t pattern to filter tables
                -b buffer size
                -c direct export
                -o owner
            
      -------
      Username and password are mandatory arguments. Default 
      for the output file is standard output. For help, 
      try -help or -h.

);

    exit(0);
}

-- 
Mladen Gogala
http://www.mgogala.com

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Jun 20 2006 - 23:56:22 CDT