snap shot too old error-001555

I have one doubt regarding snap shot too old error.

If the oracle server is not able to provide read consistancy image of data the we will get snap shot too old error.

Suppose if user A is updating some rows then old image will be there in roll back segment and new value in datablock.Now if user B tried to select it will take values from roll back that is read consistancy.If user B is unable to get value from roll back he will get snap shot too old error.Now user A gives commit so that old image will be erased and user B will get snap shot too old error.My doubt is if user A commits then that value will be there in data block and now it is consintant so now from where user B will retrieve data ie from data segment or from roll back segment..?.If user B again looking into roll back then why because already user A commited then why again user B looking into roll back.

Please clear this doubt ....

Quote:

---

My doubt is if user A commits then that value will be there in data block and now it is consintant so now from where user B will retrieve data ie from data segment or from roll back segment..?.

---

from data segment ...


Thanks,

When a transaction commits Oracle do updates RBS header indicating the transaction has been commited but it does nothing to the data block -delayed clean up calls for the next transaction visiting the block to take action.

At the time a new transaction visits the same datablock Oracle looks at datablock header and notices a non-commited transaction was working on that block so Oracle goes to the corresponding RBS for more information, finds out the transaction did got commited then marks the datablock header as commited.

That's why a second transaction goes for RBS even after the first transaction commits -Delayed Cleanup is your answer.

yes,If user A commits then RBS header will be updated.Ok when new transcation goes to same datablock Oracle looks at datablock header and notices that non-commited transaction was working on that block so Oracle goes to the corresponding RBS for more information, finds out the transaction did got commited then marks the datablock header as commited.At this time oracle understand that block is commited and at this stage from where it will take data from data segment or from rollback segment..I have doubt here...

If it is from data segment we wont get ora 0155 error beacuse new data is available there but if it from rbs then we will get.Please clear ..

Ask yourself what's the reason you get ORA-01555

Quote:

---

Originally Posted by tmgsam

If it is from data segment we wont get ora 0155 error beacuse new data is available there but if it from rbs then we will get.Please clear ..

---

Well as it seems from PAVB's 2nd last reply, the oracle server will direct the new transaction towards data block's header that will state dat the transaction is uncommitted bcos of delayed cleanout, so the transaction will now refer to rollback segment whose header wud contain the information dat the earlier transaction dat created this rollback segment is now committed. So the available data block in buffer cache is marked as it is of committed transaction and is the only candidate for data retrieval or DML.
But if rollback segment is not there, then all this will not happen and you will receive the error "SNAPSHOT TOO OLD"

Quote:

---

Originally Posted by tmgsam

If it is from data segment we wont get ora 0155 error beacuse new data is available there but if it from rbs then we will get.Please clear ..

---

Ever heard of undo_retention. NOw think why you may get ORA-01555 error.

Thanks PaVAB and all..

Can any one tell which algorithm is used in undo segment to flush the old value.

when undo_retention is not guaranteed, u would get read consistency error, which in turn shows this error.. snapshot too old...

My take is that if you have a long running transaction Oracle will maintain a read consistent view of the database from when the transactions started. Other transactions will continue to occur at the same time that this long running transaction is happening. At some point Oracle may find out that with the amount of redo being generated and the size of the undo tablespace that it is no longer able to maintain that read consistent view of the data and will throw the 0155 error and rollback any updates that me have already occurred. So the question becomes how do you do large dml transactions on a running database without getting this error, and the answer is to use pl/sql collections to hold the data and instead do a lot of small transactions and commit after each one. This is different from just using a cursor because the read consistent view is maintained in the collection away from the tables.

Actually, large transactions based on looping on a cursor are a source of ORA-01555.

I've troubleshooted and fixed a zillion loop-cursor based queries throwing ORA-01555, the usual mistake is to commit inside the loop which tell Oracle UNDO space can be reused while actually your query needs to have the UNDO space available until the end of the loop.