|
-
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
extents available.
1. A new extent will be allocated from the undo tablespace when the requirement
arises.
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
Good Luck
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|