Spooling dies when large number of records reached

[toddyl]

Hi,

I'm trying to do:

spool myfile.txt
select * from enormous_table where an_id = 1234;
spool off

The enormous table has over 5 million rows in it and this select is expected to pull back a couple of million rows.

I'm running this sql but for some reason my connection keeps dying and I end up with a zero sized file.

Does anyone know what is going on and how I can extract this data to file.

 

[SantaMufasa]

Toddyl,

To isolate your problem, I would confirm that everything is working properly by doing incremental proofs-of-concept of the components of what your are trying to do:

1) Spool just the first few rows of your table:

spool myfile.txt
select * from enormous_table where [b]rownum <= 10[/b];
spool off

2) Do the query without spooling. (This confirms whether or not something is awry with your spooling.):

select count(*) from enormous_table where an_id = 1234;

By doing a count(*), you don't have to "see" all the output on the screen this first pass. Also, you can get an idea of how long the query should take.

Once you have executed the "count(*)", then you can do the "SELECT *..." version of the query, but again, don't spool until you have confirmed that the non-spooling version works.

Absent other error messages, the above trouble isolating should give you/us some additional feedback.

Let us know your findings.



Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[LKBrwnDBA]

Your connection is 'timing out', create a script like:

#!/bin/ksh
. /path/to/oraenv
sqlplus -s uid/pw <<!
spool myfile.txt
select * from enormous_table where an_id = 1234;
spool off
!
exit

And execute like this:

nohup myscript&

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb