Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: SPOOL Problem
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
--
Learning Oracle, UNIX/Linux...
Love
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.
-- http://www.freelists.org/webpage/oracle-l Received on Tue Jun 06 2006 - 22:50:27 CDT