Snapshot too old error
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Thread: Snapshot too old error

  1. #1
    Join Date
    Dec 2001
    Posts
    337
    Hi ,

    I got the ORA-01555 Snapshot too old error yesterday, as a solution i increased the OPTIMAL value of the rollback segments and recreated them. However, the process fell over again today with the same error...As a result i tryed to increase the minextents to 20 as suggested by Oracle..when i ran the sql, the statement was processed...however,looking at dba_rollback_segs for new rsb; it show min_extents as 1?? I checked usin OEM and got the same result???Can anyone shed any light on this??

    Thanks..
    Chucks_k

  2. #2
    Join Date
    Dec 2001
    Posts
    221
    create a larger rollback segment for the long running queries
    Santosh Jadhav
    8i OCP DBA

  3. #3
    Join Date
    Dec 2001
    Posts
    337
    Yes..i have tried that, but as i said the statement is processed but not registered within the dba views?..

  4. #4
    Join Date
    Sep 2001
    Posts
    62
    you say you tried to increase the min extents value, did you succed ?

    What is you max_extents value ?

    The max_extents value needs to be increased if you are recieving snap shot too old errors.


  5. #5
    Join Date
    Feb 2002
    Posts
    13
    snapshot too old can best be resolved by increasing min_extents, unfortunately in order to do so, you must drop and recreate them.

  6. #6
    Join Date
    Dec 2001
    Posts
    337
    Yes i know that you need to increase minextents, however when i do so, the data dics and oem specify min_extents as 1!!..

  7. #7
    Join Date
    Apr 2001
    Location
    London
    Posts
    725
    query v$rollstat for those rollback segments without transactions.
    where xacts =0

    for those rbs's
    alter rollback segment rbs1 offline;
    drop rollback segment rbs1;

    create public rollback segment rbs1
    storage(initial next minextents etc etc)

    alter rollback segment rbs1 online;

    You CANNOT alter a segment to increase its min extents.
    The segment MUST be dropped and recreated.

    you can also just create 1 large rbs and use 'set transaction use rollback segment' statement to assign a specifiic large rbs to a large transaction.

    Read oracle documentation on RBS's, if they are currently set to min exents = 1, you are not following Oracle guidelines.


    Once you have eliminated all of the impossible,
    whatever remains however improbable,
    must be true.

  8. #8
    Join Date
    Dec 2001
    Posts
    337
    Hi Suresh,

    Thats exactly what i did...
    I know that to alter minextents, next n initial u need to drop and recreate the RSB. I re-created the RSB with the following parameters:

    create public rollback segment "XXX"
    tablespace "xxx"
    storage (initial 800k next 800k minextents 20 maxextents 250);

    When i check OEM, it shows the following parameters for the above RSB

    INITIAL EXTENT SIZE
    160000k
    NEXT
    32k
    MINEXTENTS
    1

    Maybe i am interpreting it wrong!


  9. #9
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    Snapshot too old error has 10 different reasons. First You need to understand what the application is doing. You can't eliminate this error completely.
    However, you can minimize the occurance of this error by carefully creating rollback segments; and not mixing OLTP load with batch load.

    Did you get this error from a batch load?
    Or did the online users get this error?


  10. #10
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    tamilselvan is right, it is very hard to eliminate this error as there are MANY reasons for it accuring. On piece of advice I can offer is to look at your commit rate of your process. The best alternative for BATCH type processing is to either COMMIT ONCE, or COMMIT INFREQUENTLY. This CAN reduce the lkelyhood of "snapshot to old" as caused by "COMMIT across a fetch" i.e. using a cursor to update records.

    Also,

    Oracle insists that two transactions can NOT ONLY share the same segment within a rollback segment, but also ocupy the same block. (Mind you, the occurance of this would be quite rare). Personally, the way in which Oracle handles rollback segments leaves alot to be desired.

    Cheers,

    OCP 8i, 9i DBA
    Brisbane Australia

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