| sql*plus spooling error [message #373689] |
Tue, 01 May 2001 17:09  |
MikeD
Messages: 4
Registered: May 2001
|
Junior Member |
|
|
I am new to Oracle and I am having a problem trying to create a flat file using the sql*plus spool command. My query runs for 90 minutes and then I get the message: ORA-01555: snapshot too old: rollback segment number 29 with name "R0108" too small. My query is just selecting records and spooling them to a file and I should get 1M+ records. There should be no inserting/updating of records occurring in my table while I running, so I don't understand why I'm getting this message. I've asked around and people aren't sure why this is happening either and recommend splitting up my query so that I retrieve less records at one time. However, this is not easy for me to do and it just seems like it should not be a problem since I'm not doing any updating. I'm running on a Unix machine with Solaris and Oracle 8i and SQL*Plus 3.3.4.0.1. Thanks in advance for any help you can give me. Here is my query:
sqlplus
set heading off;
set term off;
set verify off;
set linesize 366;
set feedback off;
set wrap off;
set pagesize 0;
set space 0;
column back_da format A8;
column foward_da format A8;
column so_da1 format A8;
column so_da2 format A8;
column insert_da format A20;
spool /tmp/spool-data;
select d_type, first_name, year,
order_number,
control_number,
first_initial,
src_input,
to_char(back_date,'YYYYMMDD') back_da,
to_char(foward_date,'YYYYMMDD') forward_da,
uxi,
format, size, location, part_number,
to_char(so_date1,'YYYYMMDD') so_da1,
to_char(so_date2,'YYYYMMDD') so_da2,
to_char(insert_date,'YYYY-MM-DD HH24:MI:SS') insert_da"
from prod_table
where part_number is not NULL;
spool off;
exit;
|
|
|
|
| Re: sql*plus spooling error [message #373690 is a reply to message #373689] |
Tue, 01 May 2001 18:21  |
Andrew again...
Messages: 270
Registered: July 2000
|
Senior Member |
|
|
In simple terms, oracle has a tradeoff to make between preserving your query result until you've received all the data and freeing space for changes made by new DML statements since your query began. You can try to minimise the time it takes for you to spool data to speed things up.
1.) set trimspool on; -- This trims trailing blanks off the output, else each line is as long as you have set in linesize.
2.) set termout off; -- suppresses terminal output while spooling. To make this work, I think your query probably needs to run from a script, not an interactive command. This should make a big difference.
|
|
|
|
| Re: sql*plus spooling error [message #373710 is a reply to message #373690] |
Wed, 02 May 2001 08:34 |
MikeD
Messages: 4
Registered: May 2001
|
Junior Member |
|
|
|
Thanks for the reply and the suggestions. However, my records do not have any trailing blanks so the set trimspool on will not help. Also, I have run this from a script with set termout off and I still get the error. The thing I guess I was hoping for was some setting that would tell Oracle not to hold my query result until it is completely finished. All I want to do is spool each record to a flat file and I don't need to any additional processing on the full result set.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
