Oracle Table millions of row, pull and save into file

I have oracle table that holds more than 30 million records, I need to pull all that data into file and 开发者_JAVA技巧store it.. Can anyone suggest me what will be easiest way to do that and what kind of file do I have to use to store all that data. If there is a way I can put data into multiple files that will work too.. You can tell me manual or programatical method. Thank you

Ask Tom has an answer http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::p11_question_id:88212348059

Also, you could do it in SQL*Plus by doing a select with spool on (from a shell script)

sqlplus /nolog <<EOF
conn /as sysdba
set pagesize 0 heading off feedback off verify off echo off trimspool on
spool test.log
select 'dsmc inc "'||file_name||'">>hot_WISDOM_$BCKNAME.log' from dba_data_files
 where rownum<5;
spool off;
exit
EOF

More info on the spool stuff at http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:25323432223677

That page is where I found that snippet above.

You will want to replace the connection details with your username / pw.

Starting with Oracle 10g you can create a new table with ORGANIZATION EXTERNAL and INSERT records from the original table into it. See this example from the Oracle 10gR2 documentation.

One of the easiest formats to store data in is comma seperated value (.csv). You can define your delimiter to be any character (comma is a default) as long as you know the delimiter when you are parsing it. (For instance, the ~ character would be a good decision). Usually this format can easily be opened in a spreadsheet program like Excel. It can also be easily parsed to be re-inserted into a database of your choosing.

To export from oracle, you can do something like:

spool backup.csv;
select column1||','||column2||','|| ... from table; 
spool off;

After exporting, here is a resource for working with csv files in perl:

http://perlmeme.org/tutorials/parsing_csv.html

Apache commons has a great library for java:

http://commons.apache.org/sandbox/csv/apidocs/org/apache/commons/csv/CSVParser.html