ora-01555: snapshot too old

[badrinathn]

Hi,
I have set to use a bigger rollback segment call
REI_DATA_LOAD by giving
set transaction use rollback segment rei_data_load.

and I have put a query to get the number of records from the table from which the data is being deleted at background.

I get this err:
ORA-1555: snapshot too old: rollback segment number 5 with name 'RBS4'....too small

But with my setting I am using a bigger RBS while the error shows as a diff one

Can any of you explain.

Badrinath;

[contactrb]

Is it your set trans command the first command in that transaction.

Are you sure that is sucessfully executed.

If not pl! try . Let me know after that.

[badrinathn]

After I had set the transaction rbs, I was trying a sql which had failed.
Does it mean that when you set the transaction RBS, it is valid for only one transaction , whether I give commit/rollback statement.

Anyway, after I re opened the session and it started working.
Badrinath

[jmodic]

you are performing a select, you are not changing any records, so it is totally irrelevant which RBS you set your transaction to use! Remember, setting the transaction to use a specific RBS means that the changes *this transaction* will make will be recorded in that RBS.

In your case the data in the table you are selecting from are changed by *other* transactions, and those other transactions are using other rollback segments, not the one you have chosen for your "transaction"! At specific point in time your long-running select tried to create a read-consistent view of some block that once was stored in RBS4, but has allready been overwritten by some other transactions. So because your select was not able to read the whole dataset as it was at the moment the query started, it failed with ORA-1555.

Search in this forum for ORA-1555, there should be at least dozen of threads with more detailed explanations why ORA-1555 happens.