Something interesting happened the other day on one of our Production Databases. The UNDO tablespace just kept filling up, without showing any downward trend. As this was a mission critical database, core-banking, we couldn't take the risk.
I check the usage of UNDO and for any other abnormal sessions or jobs that might be out of place. Nothing!!! But the space usage just kept increasing. I realized this had to be some sort of a bug. Well, after so much experience on the Oracle Databases, a DBA must have the intuition that "this must be a bug!". It was weird because the Undo Segments just wouldn't Expire... All were in the Active state.
I checked the Metalink for some time, and found a document which was very closely related to my situation.
Problem Description:
When undo tablespace is using NON-AUTOEXTEND datafiles,V$UNDOSTAT.TUNED_UNDORETENTION may be calculated too high preventing undo block from being expired and reused. In extreme cases the undo tablespace could be filled to capacity by these unexpired blocks.
To find out if you are hitting the same, check the output of the below statement:
select end_time, MAXQUERYLEN, TUNED_UNDORETENTION
from v$undostat where
trunc(end_time)>trunc(sysdate-1) order by 1,2,3;
This will show a comparison between the maxquerylen and the tuned_undoretention. Ideally, the maximum amount of time set by the automatic Undo Retention should be either the maxquerylen or the value of undo_retention parameter. But in this case, the values under TUNED_UNDORETENTION column may turn out to be much higher than the both the maxquerylen or the value of undo_retention parameter
Solution:
Set the below hidden parameter:
alter system set "_smu_debug_mode" = 33554432 scope=both;
You can see that all the UNDO segments that were ACTIVE and well passed the UNDO_RETENTION value have finally expired, and the space released from the UNDO Tablespace.
