Results 1 to 2 of 2
  1. #1
    Join Date
    May 2002

    Thumbs down Unanswered: Failure to extend rollback segment because of 30036 condition


    We have a problem with our rollback segments. I saw tons of articles and postings on the net about this problem. But I am still wondering why this problem should occur.=20

    Here is our scenario.

    DB: Oracle
    OS: SunOS 5.9 SPARC 64-bit Firesystem

    UNDO information
    undo_management AUTO
    undo_retention 10800
    undo_suppress_errors FALSE
    undo_tablespace UNDOTBS1

    The UNDO tablespace size is 2000MB with AUTOEXTEND OFF. Yesterday I saw few of the following error lines in alert_log.

    Failure to extend rollback segment because of 30036 condition

    When someone ran a process that deletes around a million rows, he received the following error.

    ORA-01555: snapshot too old: rollback segment number 2 with name "_SYSSMU2$" too small

    I starting digging what could have caused this. When I looked at v$undostat view, I see that a long running query took only 14 sec
    (MAXQUERYLEN) in the past 24 hours. Our UNDo retention is 3 hours as you see above. Also the value for column NOSPACEERRCNT is zero. That means, there were no errors with space problem. Then why did we receive 30036 and ORA-01555 errors?

    When I looked at the UNDOTBS1 usage, I saw that it was almost full for the whole day. 1950MB used, 50MB free. I think this caused the problem.
    But there are no long running queries. What was happening? I thought
    UNDOTBS1 is not going to release the space. Luckily at almost the end of the day, the space was released. Now the used space is 150MB, free space 1850MB. What is going on here? How to avoid these errors? Any suggestions are welcome.


  2. #2
    Join Date
    Jun 2003
    West Palm Beach, FL


    Maybe UNDO_RETENTION is to large, try 30 minutes (1800).

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

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