Re : Re: Download data from oracle tables to flat file

The OCI program is available : it's called PDQOUT and we sell it (price depends on how many rows you want to be able to download, but the latest multi-threaded version is under $7,000).  Honestly, it all depends on the size of your tables and how frequently you are downloading. Jared's free dump.sql utility can provide quite a decent service if you are in the hundred of thousands of rows. I have benched on a 1,000,000+ rows table (real data) and a relatively modest single-CPU server, it will give you an idea of what you can expect :

  exp conventional path    : 47 seconds
  exp direct path          : 15 seconds
  sqlplus spool (dump.sql) : 86 seconds
  pdqout V2                : 23 seconds

 I have not tried the reload, but with these figures I would expect imp to take 4 to 5 minutes, SQL*Loader with the conventional path to run in about the same time and SQL*Loader to be under one minute with both the spool and pdqout output (and probably silghtly faster with the pdqout output than with the spool output because it generates smaller files by trying to be clever with separators and date formats). Which means that if I am not too wide off the mark download+reload would be nearly twice as fast with sqlplus spool + sqlloader than with exp direct + imp, and pdqout + sqlloader almost four times as fast.

  Andy, any Perl/DBI time ?

 As I said, you will probably look differently at all this if your 'big table' has 500,000 rows or 100 million rows. You may be quite happy with exp/imp ! It all depends on how much money is time worth to you :).

-- 
HTH,

  Stephane Faroult
  email: sfaroult_at_oriolecorp.com 
  Oriole Corporation
  Voice:  +44  (0) 7050-696-269 
  Fax:    +44  (0) 7050-696-449 
  Performance Tools & Free Scripts
------------------------------------------------------------------
http://www.oriolecorp.com, designed by Oracle DBAs for Oracle DBAs
------------------------------------------------------------------


> 

> 

> Hi Balasubramanian,

> 

> > Is there any way to unload the data to flat file other than using 

> > spool on 

> > select * from <table_name>

> > For very large table having thousands of rows, the above technique will be

> > very slower. 

> > Is there any other way i can unload the data from the tables faster ?

> > Any kinf of help is appreciated.

> 

> Try Perl DBI.

> => http://www.symbolstone.org/technology/perl/DBI/index.html

> => http://forteviot.symbolstone.org/cgi/dbi/moduledump?module=DBD::Oracle

> 

> Does the trick for me.

> 

> Alternatively, you could write a Pro*C-OCI C program for really super-fast

> access, or a PL/SQL compiled routine, easier to program, probably a bit slower