he "ORA-01555 snapshot too old" error, which causes user transactions to fail, is a nightmare for Oracle DBAs and developers. It usually occurs after queries or batch processes have been running for a long time, which means you can lose many hours of processing when the error crops up.
An active database with an insufficient number of small-sized rollback segments.
A rollback segment corruption that prevents a consistent read requested by the query.
A fetch across commits while your cursor is open
low value undo retantion
SET TRANSACTION USE ROLLBACK SEGMENT Rb_Large
so u can use different ROLLBACK SEGMENT....
SET TRANSACTION USE ROLLBACK SEGMENT Rb_Large.
morover,ur quary take 1158 second u can reduce time by sql tuning .
A. UNDO SEGMENT IS SMALL - May be if you are using 8i
B. UNDO RETANTION IS LOW VALUE - May be
C. USER NOT COMMIT THERE TRANSACTION IMMEDIATLY - Wrong, have you heard about fetch across commit?
1. INCREASE THE SIZE OF UNDO TABLESPACE - May be
2. USE SPECIFIC ROLLBACK SEGMENT FOR LARGE TRANSACTION BASICALY BATCH PROCESS - Out of question in Automatic Undo
3. MAKE UNDO_SUPPRESS_ERROR=TRUE - Just make your set transaction statement gets ignore silently, I guess you have never seens this error
alter rollback segment SYSSMU8$ storage (optimal 1M)
ERROR at line 1:
ORA-30019: Illegal rollback Segment operation in Automatic Undo mode
Guys, calm down!
Liacat, since you know you are junior, be a bit more modes. Davey is an EXPERT and the fact that you are certified does not makes you bigger expert. Most of people here are certified and do not even mention that.
SQL> alter session set undo_suppress_errors = true;
SQL> set transaction use rollback segment SYSSMU1$;
That was the biggest nonesnse I have seen in that forum, so please realise you have much to learn before starting to talk like expert.
However I have to admit you are right for some things
1) ora-01555 is a night mare - as you can see NOONE gives specific recommendation although some talks like if they know what the reason could be but it is just too comlicated to explain to us, normal people
2) Fetch across commit was a good point. If the issue is 100% reproducable, that's most probably the case
At the end of the day the poster seems lost interest in that, since he is neither postin nor answering anymore
I haven't increased the undo_retention size.Since 3hrs is high for a small database that too having less number of users (18,000 registered users.).
Now,i asked application team to tune that particular query.
I can't get you exactly.can u explain in detail. How reducing UNDO_RETENTION can eliminate the error.
OK, as I said it's more like feeling, but sometimes something crazy could do the trich:-). As written in the note I mentioned, the algorithm works like that:
============================================================ Unexpired extents will not be reused until and unless there are no more free
1. A new extent will be allocated from the undo tablespace when the requirement
2. If this fails because of no available free extents and we cannot autoextend
the datafile, then Oracle tries to steal an expired extent from another undo
segment. 3. If it still fails because there are no extents with expired status then
Oracle tries to reuse an unexpired extent from the current undo segment.
5. If all the above fails, an Out-Of-Space error will be reported.
I understand like that
1) Try to use free space
2) Try to reuse expired extent from the current segment
3 ) Try to steal expired extent from other undo segment
4) REUSE UNEXPIRED EXTENT FROM THE CURRENT SEGMENT
5) TRY TO STEAL UNEXPIRED EXTENT
6) Raise an error
Now my considerations
Obviously large UNDO_RETENTION makes harder 1,2 and 3 to happen, so we move to 4. In 4 however noone says that reusing of unexpired undo comes in FIFO order. I seriously suspect that they simply reuse the next unexpired extent, regardless of how old the data in the extent are, hence we come easyer to ORA-01555
Reducing the UNDO_RETENTION will increase the number of expired extents and hence lower the probability to reuse unexpired extent
That's just a feeling but trying that is so easy that it worths to try it.
Also I would agree with Liakat that you should check if the script does not makes fetch across commit