| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Is sqlplus too slow to unload data?
I believe the scheduled date is August.
Of this year. :)
Jared
"Kimberly Smith" <ksmith2_at_myfirstlink.net>
Sent by: root_at_fatcity.com
04/25/2002 07:08 AM
Please respond to ORACLE-L
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
cc:
Subject: RE: Is sqlplus too slow to unload data?
Hey Jared,
I got quite a few folks waiting for your book now. I am really pushing
Perl at the office:-) When is the scheduled release again?
-----Original Message-----
Sent: Thursday, April 25, 2002 12:03 AM
To: Multiple recipients of list ORACLE-L
On Tuesday 23 April 2002 21:53, Bin Wang wrote:
> Hi,
> Our application uses sqlplus + sqlloader to transfer data between
> databases. It takes nearly four hours to unload to data to flat
> files(1G), which is far too slow. In the application, the query looks
> like the following. All those &3,&4,&5 are for sqlldr format.
How about using Perl?
Below is a script I just used to unload a table. Not very big, but this is just from one of my test databases at home on a Linux box.
It unloaded about 12,000 rows in less than 2 seconds. This include writing them to a file. This script creates a file of <TABLENAME>.dmp.
$> time ul.pl -database ts01 -username orades -password orades \
-table 'I$RM_PROPERTY_MAPS'
1.22s real 1.07s user 0.04s system
Doing the same thing with SQL*plus took 4.46 seconds.
You must have DBI and DBD::Oracle installed to use this.
Jared
# ul.pl - unload a table
use warnings;
use FileHandle;
use DBI;
use strict;
use Getopt::Long;
my %optctl = ();
Getopt::Long::GetOptions(
\%optctl,
"database=s",
"username=s",
"password=s",
"table=s",
"sysdba!",
"sysoper!",
"z","h","help");
#setup environment - homegrown package
my($db, $username, $password, $connectionMode);
$connectionMode = 0;
if ( $optctl{sysoper} ) { $connectionMode = 4 }
if ( $optctl{sysdba} ) { $connectionMode = 2 }
if ( ! defined($optctl{database}) ) {
Usage();
die "database required\n";
}
if ( ! defined($optctl{username}) ) {
Usage();
die "username required\n";
}
if ( ! defined($optctl{table}) ) {
Usage();
die "table required\n";
}
$username=$optctl{username};
$password = $optctl{password};
my $dbh = DBI->connect(
'dbi:Oracle:' . $db,
$username, $password,
{
RaiseError => 1,
AutoCommit => 0,
ora_session_mode => $connectionMode
}
);
die "Connect to $db failed \n" unless $dbh;
# time and adjust row cache size
$dbh->{RowCacheSize} = 5000;
my $MySql="select * from $optctl{table}";
my $sth = $dbh->prepare($MySql);
$sth->execute;
open(OUT,">$optctl{table}.dmp") || die "cannot create $optctl{table}.dmp - $!\n";
my $delimiter = '~';
no warnings; # don't raise warnings on null columns while( my $ary = $sth->fetchrow_arrayref ) {
print OUT join($delimiter,@{$ary}), "\n";
}
$sth->finish;
$dbh->disconnect;
sub Usage {
print "\n";
print "usage: ul.pl\n";
print " ul.pl -database dv07 -username scott -password
tiger -table emp
print "\n";
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: jkstill_at_cybcon.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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kimberly Smith INET: ksmith2_at_myfirstlink.net 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: Jared.Still_at_radisys.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 Thu Apr 25 2002 - 14:50:05 CDT
 |
 |