Today I was answering one of the queries, this question seriously made my interest. The question was
"I have 15 million records in my table. What do you suggest a best method for unloading them from Oracle table to a flat file".
Seriously till that time I had never done this. The need had not come. I clearly know two ways of doing this:
- Using SQL Plus and SPOOL command
- Using UTL_FILE built-in package
UTL_FILE vs SPOOL
I know that SPOOL command will be faster in execution than UTL_FILE package, but also I reminded myself that this may not be the case always. SPOOL commands operates in the client machine and depending upon the network traffic it might take time for the SPOOL operation than UTL_FILE.
SO I ADVICE TO TEST BOTH OPTIONS IN YOUR ENVIRONMENT BEFORE FINALISING ON ONE OF THE ABOVE.
I will soon publish routines for this purpose. Your patience is appreciated.
More resources for your inquisitive mind
A little surfing did a wonderful job in putting this article up. In asktom I found a beautiful document that should be the starting page for such exploration. Here is the link for you. I am not going to discuss how tom reviews this anymore. Check the link for yourself.
If you are not having Oracle client in your machine and still you need to unload the data to flat file, follow this link and introduce yourself to a tool OraCmd. I have not personally used this tool, but from the description in the website it promises to be one. I will test the performance issues with this as well in the near future. Here is the link for unloading using OraCmd.
