Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

I have to extract around 3 million records from two different databases for further processing. Currently, I execute the queries via SQL*Plus and spool the results to a plain text file.

This queries take around 10 minutes each (it's faster on one of the databases) but im looking to improve those times.

I found this tool: http://www.iri.com/products/FACT that's supposed to do exactly what I need. Do you know about any other open source tool that I can use to help me improve the extraction times, or maybe a better technique than just spooling to a text file?

share|improve this question

closed as off-topic by gnat, Kilian Foth, GlenH7, MichaelT, Robert Harvey Oct 24 '13 at 16:58

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Questions asking us to recommend a tool, library or favorite off-site resource are off-topic for Programmers as they tend to attract opinionated answers and spam. Instead, describe the problem and what has been done so far to solve it." – gnat, Kilian Foth, GlenH7, MichaelT, Robert Harvey
If this question can be reworded to fit the rules in the help center, please edit the question.

    
did you looked at MS SSIS packages ? –  Yusubov Jul 27 '12 at 18:30
    
A good product won't be always free. –  pandu Jul 27 '12 at 18:32
1  
Have you tried the Oracle datapump or SQL*Loader? datapump: oracle-base.com/articles/10g/oracle-data-pump-10g.php SQL*Loader: orafaq.com/wiki/SQL%2ALoader_FAQ –  FrustratedWithFormsDesigner Jul 27 '12 at 18:41
1  
For further processing by what? The whole point of putting data in a database is to make it easy to process. –  Jon Strayer Jul 28 '12 at 23:10
    
Do you extract a query or do you extract a table? It may be quicker to make a new table for extraction and then to extract that table using the direct path option, "direct=Y". Also see dba.stackexchange.com –  tsundoku Aug 8 '12 at 10:41
add comment

2 Answers

I do not know much about the set up of your application, but here is one thing that can be done without using any tool. The log() method can be used from a java application(if you set up one). You just need to import the log4j.jar and it will output your records in your log file. Obviously this involves only a little effort and no extra money.

share|improve this answer
add comment

I have used Kettle Pentaho for that same situations. It's faster than SQL+ spooling when there are millions of records.

It's free and open source.

share|improve this answer
add comment

Not the answer you're looking for? Browse other questions tagged or ask your own question.