snap shot too old error-001555
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: snap shot too old error-001555

  1. #1
    Join Date
    Feb 2009
    Posts
    49

    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 ....

  2. #2
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    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.

  3. #3
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    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.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    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.

  4. #4
    Join Date
    Feb 2009
    Posts
    49
    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 ..

  5. #5
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Ask yourself what's the reason you get ORA-01555
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    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.

  6. #6
    Join Date
    Oct 2008
    Location
    Chandigarh, India
    Posts
    322
    Quote Originally Posted by tmgsam View Post
    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"
    lucky

  7. #7
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    Quote Originally Posted by tmgsam View Post
    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.
    http://www.perf-engg.com
    A performance engineering forum

  8. #8
    Join Date
    Feb 2009
    Posts
    49
    Thanks PaVAB and all..

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

  9. #9
    Join Date
    Apr 2008
    Posts
    59
    when undo_retention is not guaranteed, u would get read consistency error, which in turn shows this error.. snapshot too old...

  10. #10
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,021
    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.
    this space intentionally left blank

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width