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.
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,
Vijay Tummala
Try hard to get what you like OR you will be forced to like what you get.
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.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
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 ..
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
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"
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.
Reply With Quote
Originally Posted by tmgsam
Bookmarks