| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> ORA-01555 Snapshot Too Old
The same weekly job has been blowing up with "snapshot too old" for the last year. After having been awakened (again) at 3:00 am, I'm a desperate woman. Any ideas would be greatly appreciated.
We get the ORA-1555 error every time we run this job. We get this error if the job is the only process on the system. We get this error even if we fire up Steve Adams' job to hang a transaction in the same rollback segment with an uncommitted transaction. (Steve's job has definitely helped - we run significantly longer than we used to before we get the error.)
The specifics:
The job belongs to our vendor. It's junk. Our development
staff does not have time to rewrite it. The vendor has been unable
The job is written in Cobol. (No, I'm not making this up).
The rollback segment we're using (via "set transaction use
rollback segment ...") is the only rollback segment in this particular
tablespace. The tablespace size is 1500 megs.
I've tried a number of configurations for the rollback
segment. Currently it's set like this: initial 20m, next 20m, min extents
40 (ya, I know...). So, initially 800m of the 1500m tablespace is set aside
for this rollback segment. The rollback segment currently does not have
optimal set. I've tried different configurations, but none have been
successful.
It always takes 3 runs to complete this job: First run fails with snapshot too old. The rollback segment NEVER EXTENDS. (i.e., it has only the initial 40 extents -- even tho there's another 700 megs available, it never attempts to extend. ) After a restart, the second run fails because it actually does extend, and then it runs the 1.5 gig tablespace out of space. Third run successfully completes.
The job is deleting about 2 million records from a table of about 5 million records. It "seems" to select via an index, delete by row id, select,delete,,,, I believe the snapshot too old comes about because, at the very end, the job somehow tries to query the table it's been deleting from, and would therefore need to read the records from its own rollback space.
The database is 7.3.4 (No, I'm not making this up.)
Any ideas?? I'm mostly confused about why Steve's job to hang the transaction is not preventing re-writing the rollback segment.
Thanks for any help.
Barb
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Baker, Barbara INET: bakerb_at_rockymountainnews.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-LReceived on Thu Oct 18 2001 - 15:30:44 CDT
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
 |
 |