Ora-01555 - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 25

Thread: Ora-01555

  1. #11
    Join Date
    Feb 2007
    Location
    UNDER MOON
    Posts
    44

    ora-01555

    Quote Originally Posted by davey23uk
    A, C are wrong solutions 1,2,3 are wrong

    hi....

    wht solution u lies here.


    liakat hosain

  2. #12
    Join Date
    Feb 2007
    Location
    UNDER MOON
    Posts
    44
    he "ORA-01555 snapshot too old" error, which causes user transactions to fail, is a nightmare for Oracle DBAs and developers. It usually occurs after queries or batch processes have been running for a long time, which means you can lose many hours of processing when the error crops up.
    POSSIBLE RESONS..
    An active database with an insufficient number of small-sized rollback segments.
    A rollback segment corruption that prevents a consistent read requested by the query.
    A fetch across commits while your cursor is open
    low value undo retantion

    SET TRANSACTION USE ROLLBACK SEGMENT Rb_Large

    so u can use different ROLLBACK SEGMENT....
    SET TRANSACTION USE ROLLBACK SEGMENT Rb_Large.
    morover,ur quary take 1158 second u can reduce time by sql tuning .

  3. #13
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    stop writing rubbish, you can't do that in 9i with automatic undo!

  4. #14
    Join Date
    Feb 2007
    Location
    UNDER MOON
    Posts
    44

    Ora -01555

    wht i say u "MR. GENIUS" i dont know how can ur a senior advisor!!!!!!!!!
    I DO ALL IT ORACLE 9i...
    SQL> show parameter undo_management

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    undo_management string AUTO

    SQL> show parameter undo_tablespace

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    undo_tablespace string UNDOTBS1

    SQL> show parameter undo_suppress_errors;

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    undo_suppress_errors boolean FALSE


    SQL> set transaction use rollback segment SYSSMU1$;
    set transaction use rollback segment SYSSMU1$
    *
    ERROR at line 1:
    ORA-30019: Illegal rollback Segment operation in Automatic Undo mode


    SQL> alter session set undo_suppress_errors = true;

    Session altered.

    SQL> set transaction use rollback segment SYSSMU1$;

    Transaction set.

    go to www.asktom.oracle.com
    also read artical from arup nanda who is dba of the year 2003.

    RUBBISH ADVISOR.

    Quote Originally Posted by davey23uk
    stop writing rubbish, you can't do that in 9i with automatic undo!

  5. #15
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    you know suppress undo errors, its not actually doing what you want - its just ignoring the error!!!

    if you wish to keep trotting out rubbish, feel free but you will be called on it everytime
    Last edited by davey23uk; 02-18-2007 at 07:49 AM.

  6. #16
    Join Date
    Feb 2007
    Posts
    1

    ora -555

    There are several reasons ora-01555 snapshot too old but I think LIAKAT offer key explanation.

    U can not say like that!!!!!.

    U cannot disappointed a new writer!!! .

    better u give ur own solution user only accept wht is working




    Quote Originally Posted by davey23uk
    stop writing rubbish, you can't do that in 9i with automatic undo!
    Last edited by willim; 02-18-2007 at 11:52 PM. Reason: add line

  7. #17
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    Quote Originally Posted by willim
    There are several reasons ora-01555 snapshot too old but I think LIAKAT offer key explanation.

    U can not say like that!!!!!.

    U cannot disappointed a new writer!!! .

    better u give ur own solution user only accept wht is working
    Willem or Liakat, how should I name you? Next time try use another IP address.

    Regarding your solution Willim, since you pointed us to asktom, read carefully this thread

    http://asktom.oracle.com/pls/asktom/...:4273981551976


    A. UNDO SEGMENT IS SMALL - May be if you are using 8i
    B. UNDO RETANTION IS LOW VALUE - May be
    C. USER NOT COMMIT THERE TRANSACTION IMMEDIATLY - Wrong, have you heard about fetch across commit?

    1. INCREASE THE SIZE OF UNDO TABLESPACE - May be
    2. USE SPECIFIC ROLLBACK SEGMENT FOR LARGE TRANSACTION BASICALY BATCH PROCESS - Out of question in Automatic Undo
    3. MAKE UNDO_SUPPRESS_ERROR=TRUE - Just make your set transaction statement gets ignore silently, I guess you have never seens this error

    alter rollback segment SYSSMU8$ storage (optimal 1M)
    *
    ERROR at line 1:
    ORA-30019: Illegal rollback Segment operation in Automatic Undo mode

  8. #18
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    Guys, calm down!
    Liacat, since you know you are junior, be a bit more modes. Davey is an EXPERT and the fact that you are certified does not makes you bigger expert. Most of people here are certified and do not even mention that.
    ================================
    SQL> alter session set undo_suppress_errors = true;
    Session altered.
    SQL> set transaction use rollback segment SYSSMU1$;
    Transaction set.
    ==========================

    That was the biggest nonesnse I have seen in that forum, so please realise you have much to learn before starting to talk like expert.
    However I have to admit you are right for some things
    1) ora-01555 is a night mare - as you can see NOONE gives specific recommendation although some talks like if they know what the reason could be but it is just too comlicated to explain to us, normal people
    2) Fetch across commit was a good point. If the issue is 100% reproducable, that's most probably the case
    At the end of the day the poster seems lost interest in that, since he is neither postin nor answering anymore

  9. #19
    Join Date
    Jan 2007
    Posts
    231
    Thanks to all.

    I haven't increased the undo_retention size.Since 3hrs is high for a small database that too having less number of users (18,000 registered users.).
    Now,i asked application team to tune that particular query.

    Bore:
    I can't get you exactly.can u explain in detail. How reducing UNDO_RETENTION can eliminate the error.

  10. #20
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    OK, as I said it's more like feeling, but sometimes something crazy could do the trich:-). As written in the note I mentioned, the algorithm works like that:

    ============================================================
    Unexpired extents will not be reused until and unless there are no more free
    extents available.

    1. A new extent will be allocated from the undo tablespace when the requirement
    arises.
    2. If this fails because of no available free extents and we cannot autoextend
    the datafile, then Oracle tries to steal an expired extent from another undo
    segment.
    3. If it still fails because there are no extents with expired status then
    Oracle tries to reuse an unexpired extent from the current undo segment.

    5. If all the above fails, an Out-Of-Space error will be reported.
    ====================================================
    I understand like that
    1) Try to use free space
    2) Try to reuse expired extent from the current segment
    3 ) Try to steal expired extent from other undo segment
    4) REUSE UNEXPIRED EXTENT FROM THE CURRENT SEGMENT
    5) TRY TO STEAL UNEXPIRED EXTENT
    6) Raise an error

    Now my considerations
    Obviously large UNDO_RETENTION makes harder 1,2 and 3 to happen, so we move to 4. In 4 however noone says that reusing of unexpired undo comes in FIFO order. I seriously suspect that they simply reuse the next unexpired extent, regardless of how old the data in the extent are, hence we come easyer to ORA-01555
    Reducing the UNDO_RETENTION will increase the number of expired extents and hence lower the probability to reuse unexpired extent
    That's just a feeling but trying that is so easy that it worths to try it.

    Also I would agree with Liakat that you should check if the script does not makes fetch across commit

    Good Luck

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