Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Marcio.

Asked: August 13, 2012 - 5:33 pm UTC

Last updated: August 15, 2012 - 1:52 pm UTC

Version: 11.1.0.7

Viewed 50K+ times! This question is

You Asked

Hello,

My problem: (Today: 08/13/2012)
UNDO tablespace ever full.

My scenario:
Product: Oracle Server - Enterprise Edition
Product Version: 11.1.0.7
Operating System: Microsoft Windows (32-bit)
OS Version: 2003 R2

My UNDO parameters:
undo_management: AUTO
undo_tablespace: UNDOTBS1
undo_retention: 5400
AUTOEXTENSIBLE: NO
SEGMENT_SPACE_MANAGEMENT: MANUAL
RETENTION: NOGUARANTEE


Problem Description:
Even without active transaction, the number of UNEXPIRED and EXPIRED extensions do not diminish. Staying always FULL. No errors code found, only warnings about full tablespace.

When I investigated dba_hist_undostat view, I found (UNXPBLKREUCNT column) =0.

select tablespace_name,STATUS,count(*),ceil(sum(bytes/1024/1024)) MB_SZ from dba_undo_extents group by tablespace_name,status;

TABLESPACE_NAME STATUS COUNT(*) MB_SZ
------------------------------ --------- ---------- ----------
UNDOTBS1 UNEXPIRED 1814 1504
UNDOTBS1 EXPIRED 706 493

select ceil(sum(RSSIZE)/1024/1024) "UNDO Current Used Size (MB)" from v$rollstat a;

UNDO Current Used Size (MB)
---------------------------
1397

select (sum(c.bytes)/1024/1024) "UNDO Tblspce Total Size (MB)" from dba_tablespaces b , dba_data_files c
where b.tablespace_name = c.tablespace_name and b.contents = 'UNDO' ;

UNDO Tblspce Total Size (MB)
----------------------------
2000

Tablespace Size (MB) Free (MB) % Free % Used
------------------------------ ---------- ---------- ---------- ----------
UNDOTBS1 2000 3 0 100

and Tom said...

that is fine, you have space - it is great.

We do not release undo space typically - you *might* need it and that space isn't going to be used for anything else other than undo!!!

it is expensive to allocate things
it is expensive to deallocate things
it is undo you might want to have for a flashback query or just consistent reads
it isn't hurting anyone by being there (in fact it is helping)


It all looks great to me. (don't look at your TEMP tablespace, it does the same thing!!!! We allocate space and sort of want to hang onto it - it is expensive to allocate/deallocate - so we do it once and hang onto it for a long time).


Are you encountering any issues - unable to allocate messages? Your undo tablespace will never shrink by itself, it'll only grow by itself.

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

"UNDO Tablespace full"

Marcio Almeida, August 15, 2012 - 2:30 pm UTC

Thank you for your support and time !

Undo tbs on 11g

A reader, August 16, 2012 - 7:53 pm UTC


The behavior for UNDO tablespaces have changed quite a bit from 11g onwards. We faced similar problems and that is what was told to us by Oracle Support. You might want to check is retention guarantee is enforced for the undo tbs.

It wasn't in our case but still we had the same problem as times.

Cheers.


Tony, February 14, 2014 - 3:42 pm UTC

Regarding the previous remarks, I was checking UNDO tablespace usage with a totally different statement, and I was getting what I thought was valuable data : often very low numbers, and occasionally high. Almost never hitting the actual maximum.

Given that is totally different from the initial question stating that UNDO is always "used completely", what exactly is different from this query then :

select round(USAGEMB)
from (select sum(bytes)/1024/1024/1024 SIZEMB, b.tablespace_name
from dba_data_files a, dba_tablespaces b
where a.tablespace_name = b.tablespace_name
and b.contents = 'UNDO'
and upper(b.tablespace_name)=upper('$undo_instance_1')
group by b.tablespace_name) a,
(select c.tablespace_name, sum(bytes)/1024/1024/1024 USAGEMB
from DBA_UNDO_EXTENTS c
where status <> 'EXPIRED'
and upper(c.tablespace_name)=upper('$undo_instance_1')
group by c.tablespace_name) b
where a.tablespace_name=b.tablespace_name
and upper(b.tablespace_name)=upper('$undo_instance_1');


Is this a correct way to measure UNDO usage, or is this attempt really not doing that ?
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.