09-28-07, 13:18 #1Registered User
- Join Date
- May 2002
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 220.127.116.11
OS: SunOS 5.9 SPARC 64-bit Firesystem
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.
09-28-07, 14:00 #2Registered User
- 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