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.
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.
Jurij Modic ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?