snapshot too old

Hi friends,
I had set the transaction to use rb1 and then run a command. The execution failed saying that snapshot is too old. So, I modified the command and then tried to run it again. But this time when I tried to set the transaction to rb1, it comes up with "ora-01453 set transaction must be first statement of transaction". What should I do to execute the revised command but using the same rb1?

Also, in the "snapshot too old error", I noticed that the error is specifying a different rbs than the one on which I had set the transaction. Is it possible or is there any wrong in my execting sequence?

sqlplus>set transaction use rollback segment rb1;
sqlplus>select count(*) from .......;
error noora-01555 snapshot too old. rollback segment number 14 with name "rb2" too small.
Now, I modified the select count statement, and then
sqlplus>set transaction use rollback segment rb1;
error no. ora-01453

Can someone help me?
Thanks
manjunath

Try rollback or commit and then again issue the set transaction command.
cheers

1. how many rbs do you have??
2. what are the # of concurrent transactions taking place in the db??
3. are the rbs created with optimal size??
4. what is the value of transactions_per_rbs in init.ora??
5. paste output of:
select a.rollname, b.* from v$rollname a, v$rollbstat b where a.usn=b.usn;

Hi,
See the error u are getting is because the transaction is not fitting into the rollback segment.When u are starting the transaction Oracle assigns a rollback segment and updates its transaction table with the information.So when the transaction comes to the last extent and still it is not complete it overwrites the transaction header information and it gives u "snapshot too old " error.
The solution to the problem is to create a rollback segment with minextents set to 20.
Regarding ur "set transaction use rollback segment " statement it should be the first in the transaction.U commit or rollback ur earlier transaction and then give the statement.
In case of any help please be free to ask me at rohitsn@altavista.com

Regards,
Rohit Nirkhe,Oracle DBA,OCP 8i
rohitsn@altavista.com

Quote:

---

Originally posted by rohitsn
Hi,
See the error u are getting is because the transaction is not fitting into the rollback segment.When u are starting the transaction Oracle assigns a rollback segment and updates its transaction table with the information.So when the transaction comes to the last extent and still it is not complete it overwrites the transaction header information and it gives u "snapshot too old " error.

---

Your description has nothing to do with ORA-1555. It is not the size of the transaction itself that is causing this error. You can easilly get this error even if you don't perform any transaction at all, for example during a long-running select, when you don't cahnge any data, so you don't directly use any rollback segment at all! Generaly the main reason for snapshot too old is the fact that the select you are doing (implicit or explicit cursor) can not construct a read consistent view of changed blocks that must be read during the query.

Let's say you start your transaction in time T0. You are fetching records from table tabA, process the row and write new informations to some other tables. For the undo informations you assign the rollback rb1 to your transaction. So any changes you are performing to the database are written only to rb1.

In time T1 some other user userB modify database block b1 that belongs to your table tabA. Let's say the undo information is written into rb14. When he commits, the block in the rb14 that holds the previous immage of data block b1 is marked as "free", available to be used by other transactions.

Suppose in time T2 your transaction need to fetch some record from block b1. It detects that this blocks was changed since the transaction has begun, so it searches for the original image of this block and finds it in rb14. So far so good, you arent getting any errors yet.

But let's say in time T3 some other transaction overwrites the block in rb14 that held the image of the tabA's block b1 as it was in the time T0. Now in T4 your transaction needs to read some other row from block b1. It finds that this block has been changed and is directed to search for the pre-changed image in rb14. But it finds out that this block in rb14 was allready overwritten by some other transaction. And in this moment your transaction will bomb out with ORA-1555 "Snapshot too old". That is why the message will complain abour RB segment rb14 and not the rb1 that your transaction was actually using.

Quote:

---

The solution to the problem is to create a rollback segment with minextents set to 20.

---

Hm, why 20? In some cases 5 will be enough, while in others 2000 might be too low! There is no fast rule in setting of the minextents in order to prevent ORA-1555. The only fact is: the more rb segments you have and the larger those segments are, less chances are you will get ORA-1555. But you can never 100% eliminate the possibility of this error, particullary if long running queries (transactions) are executed when there are a lot of other transactions going on.

Hi, 24th May 2001 14:29 hrs chennai

have a look on this thread.

http://www.dbasupport.com/forums/sho...threadid=10502

Cheers

Padmam