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