The following perl script will dump entire table to a
flat file, much faster than sqlplus spool.
Please test it in your case and let me know your
result.
Thanks
Charlie cs
#!/usr/bin/perl
use strict;
use DBI;
use DBD::Oracle qw(:ora_types);
my $instance = ('dev');
my $username = ('test');
my $passwd = ('test');
my $dbh;
#declare variable for DBI use
$dbh = DBI->connect ( "dbi:Oracle:$instance",
"$username",
"$passwd",
{
PrintError => 1,
RaiseError => 1,
AutoCommit => 1
}
) || die "Database Connection
not made", $dbh->errstr;
$dbh->{LongReadLen} = 80000;
$dbh->{LongTruncOk} = 1;
my $startAll = time;
my $row;
my @tables;
my $sth = $dbh->prepare("select table_name from
user_tables");
$sth->execute();
while($row = $sth->fetchrow_array){
@tables = (@tables,$row);
}
my $startTable;
LINE: foreach my $table (@tables){
$startTable = time;
$sth = $dbh->prepare("select * from $table");
$sth->execute();
my $s;
open TABLE, ">>$table.txt";
while($row = $sth->fetchrow_arrayref){
print TABLE join "\t", (map {($s = $_) =~
s/["\\]/\\$&/g;
qq("$s")} @$row), "\n";
}
close TABLE;
$sth->finish();
print $table, " took " , (time-$startTable), "
seconds\n";
}
print "total time : ", (time-$startAll), "
seconds\n";
__________________________________
Do you Yahoo!?
Yahoo! Mail - You care about security. So do we.
http://promotions.yahoo.com/new_mail
