You would probably be best to write a PL/SQL procedure to achieve this making use of UTL_FILE to write directly to a file rather than via the SPOOL mechanism in SQL*Plus.

Using PL/SQL gives the ability to perform BULK fetches from the source data too which can significantly improve performance.

If you look hard enough, you will probably find some code on the web that will unload to CSV or SQL*Loader format text files.

What is the purpose of writing the tables to text file? Do you need to archive the data in a portable format? Loading data to another system? Different platform/rdbms?

Cheers

Graeme Farmer

From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of "Yongping Yao" <yaoyongping@gmail.com>
Sent: Wednesday, 7 June 2006 1:26 PM
To: "Graeme.Farmer
Cc: oracle-l@freelists.org
Subject: Re: SPOOL Problem

Well, I want to "unload" a table to text file( or files, as I do now, just to keep each file small). That would be more than 10 GB. There is some tools to do it, but they run on Windows( for instance, Golden has a good tool to "unload", TOAD has some memory problem too. ).

On 6/7/06, Graeme.Farmer@mincom.com <Graeme.Farmer@mincom.com > wrote:

I would expect that it is an SQL*Plus bug as this is the program that is responsible for accepting the data from the RDBMS and outputting it to file. (The fact that the realloc system call is unhandled would indicate to me that the memory allocation routine is not being tested which is certainly a bug).

Typically a program would read the incoming data (from the RDBMS) into a buffer and then writing from that buffer to the output destination (eg terminal/disk file).

If memory is continuously being allocated to increase the size of the buffer but it is not being released then this leads to a memory leak.

Of course, there could be another reason for your problem so best to raise a TAR and have the guys that write the software give you the definitive answer. To be honest, I've never tried creating a spool file that large and it may be that the developers of SQL*Plus didn't anticipate users would have this requirement either!

What are you actually trying to achieve, there may be an alternative solution?

Cheers

Graeme Farmer

--
Yao Yongping
Learning Oracle, UNIX/Linux...
Love Reading, Classical Music, Philosophy, Economics etc.
Blog: http://spaces.msn.com/members/yaoyp/

This transmission is for the intended addressee only and is confidential information. If you have received this transmission in error, please notify the sender and delete the transmission. The contents of this e-mail are the opinion of the writer only and are not endorsed by the Mincom Group of companies unless expressly stated otherwise.