Snapshot too old...
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Snapshot too old...

  1. #1
    Join Date
    Oct 2000
    Location
    Dallas:TX:USA
    Posts
    407
    While executing a procedure, I am getting the 'snapshot too old...' error --

    SQL> exec sms.computeloadterminaltm
    begin sms.computeloadterminaltm; end;

    *
    ERROR at line 1:
    ORA-01555: snapshot too old: rollback segment number 7 with name "RBS4" too small
    ORA-06512: at "SMS.COMPUTELOADTERMINALTM", line 24




    I don't think though that increasing the rollback size will help because there is plenty of space in the tablespace for the rollback to grow but the high-water mark shows that it's not extending beyond initial 2 extents (mening it does not need more space for rollback)

    The procedure involves a long group-by query that sends results into a table (insert into...)

    Any idea why this "snapshot..." error when there is room for rollback to grow ?

    Thanks,
    - Rajeev


  2. #2
    Join Date
    Jun 2000
    Posts
    179

    Talking

    Make a new set of rollback segments with a big OPTIMAL value
    Hisham Nagia
    IT Manager For Development
    Oracle Consultant - OCP

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    you better set initial extents to at least 20. The size of tablespace is not the problem here, the problem is the extent in RBS4 which a transaction is using has been overwritten by another transaction while you were querying that extent.

    When a rollback segment grows, it does this circularly, if the next extent which it wants to extend to is occupied it must *spawn* another extent, if then the occupied extents is freed it will be eligible again to be overwritten by another transactions, now if a long query started just before the extent was freed and reaches the freed extent it will find an inconsistency because that extent would be overwritten by other transactions and causing SNAPSHOT TOO OLD error. To avoid this you need high number of initial extents and bigger size extents.

  4. #4
    Join Date
    Oct 2000
    Location
    Dallas:TX:USA
    Posts
    407
    Thanks Pando and hnagia.
    For a DSS system which of the following Rollback segment configuration do you think is better and why ?

    inital : 200M
    next : 10M
    minimum : 1
    optimal 300M (or should it be 200M)

    Or

    inital : 10MB
    next : 10MB
    minimum : 20
    optimal 300M

    Thanks,
    - Rajeev

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,095
    For a great explanation, check out [url]http://osi.oracle.com/wa/ask/owa/ask_tom_pkg.display?p_dispid=275215756923[/url]
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  6. #6
    Join Date
    Oct 2000
    Location
    Dallas:TX:USA
    Posts
    407
    Thanks marist,
    that site was really helpful.

    - Rajeev

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