I got the ORA-01555 Snapshot too old error yesterday, as a solution i increased the OPTIMAL value of the rollback segments and recreated them. However, the process fell over again today with the same error...As a result i tryed to increase the minextents to 20 as suggested by Oracle..when i ran the sql, the statement was processed...however,looking at dba_rollback_segs for new rsb; it show min_extents as 1?? I checked usin OEM and got the same result???Can anyone shed any light on this??
create a larger rollback segment for the long running queries
8i OCP DBA
Yes..i have tried that, but as i said the statement is processed but not registered within the dba views?..
you say you tried to increase the min extents value, did you succed ?
What is you max_extents value ?
The max_extents value needs to be increased if you are recieving snap shot too old errors.
snapshot too old can best be resolved by increasing min_extents, unfortunately in order to do so, you must drop and recreate them.
Yes i know that you need to increase minextents, however when i do so, the data dics and oem specify min_extents as 1!!..
query v$rollstat for those rollback segments without transactions.
where xacts =0
for those rbs's
alter rollback segment rbs1 offline;
drop rollback segment rbs1;
create public rollback segment rbs1
storage(initial next minextents etc etc)
alter rollback segment rbs1 online;
You CANNOT alter a segment to increase its min extents.
The segment MUST be dropped and recreated.
you can also just create 1 large rbs and use 'set transaction use rollback segment' statement to assign a specifiic large rbs to a large transaction.
Read oracle documentation on RBS's, if they are currently set to min exents = 1, you are not following Oracle guidelines.
Once you have eliminated all of the impossible,
whatever remains however improbable,
must be true.
Thats exactly what i did...
I know that to alter minextents, next n initial u need to drop and recreate the RSB. I re-created the RSB with the following parameters:
create public rollback segment "XXX"
storage (initial 800k next 800k minextents 20 maxextents 250);
When i check OEM, it shows the following parameters for the above RSB
INITIAL EXTENT SIZE
Maybe i am interpreting it wrong!
Snapshot too old error has 10 different reasons. First You need to understand what the application is doing. You can't eliminate this error completely.
However, you can minimize the occurance of this error by carefully creating rollback segments; and not mixing OLTP load with batch load.
Did you get this error from a batch load?
Or did the online users get this error?
tamilselvan is right, it is very hard to eliminate this error as there are MANY reasons for it accuring. On piece of advice I can offer is to look at your commit rate of your process. The best alternative for BATCH type processing is to either COMMIT ONCE, or COMMIT INFREQUENTLY. This CAN reduce the lkelyhood of "snapshot to old" as caused by "COMMIT across a fetch" i.e. using a cursor to update records.
Oracle insists that two transactions can NOT ONLY share the same segment within a rollback segment, but also ocupy the same block. (Mind you, the occurance of this would be quite rare). Personally, the way in which Oracle handles rollback segments leaves alot to be desired.
OCP 8i, 9i DBA
Click Here to Expand Forum to Full Width