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 ==================================================== #!/usr/bin/perl # 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"; } $db=$optctl{database}; 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"; } use warnings; $sth->finish; $dbh->disconnect; sub Usage { print "\n"; print "usage: ul.pl\n"; print " ul.pl -database dv07 -username scott -password tiger -table emp [-sysdba || -sysoper]\n"; print "\n"; } -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).