Skip to content
Home » Oracle » Oracle Undo Tablespace Usage

Oracle Undo Tablespace Usage

The first purpose of using UNDO is for storing all uncommitted data in case we need to undo uncommitted data in rollback or after system failures. As you know, UNDO could be growing very fast during system busy hours. If you ignore undo tablespace usage, some transactions may fail.

In this post, we will discuss how to check overall and separate user usage on UNDO.

UNDO Tablespace Usage

Let's see how we check the overall usage of UNDO by this query as below.

SQL> column tablespace format a20;
SQL> column sum_in_mb format 999999.99;
SQL> select tablespace_name tablespace, status, sum(bytes)/1024/1024 sum_in_mb, count(*) counts from dba_undo_extents group by tablespace_name, status order by 1,2;

TABLESPACE           STATUS     SUM_IN_MB     COUNTS
-------------------- --------- ---------- ----------
UNDOTBS1             ACTIVE          1.00          1
UNDOTBS1             EXPIRED      1063.19       1015
UNDOTBS1             UNEXPIRED     241.94        152
UNDOTBS2             ACTIVE          2.00          2
UNDOTBS2             EXPIRED     12523.14       1456
UNDOTBS2             UNEXPIRED     357.10        265

6 rows selected.

The space is not full yet, and lots of EXPIRED extents in UNDOTBS2 which is an UNDO tablespace dedicated for node 2 instance in a RAC environment. Normally, once System Monitor (SMON) detects space pressure, it either autoextend file size or reclaim EXPIRED extents.

The statement is extremely useful when several failed transactions are rolling back by SMON in the background. You can see how much ACTIVE undo needs to be processed.

You should be aware of ORA-30036 under a very special condition: If your database throws ORA-30036 without reclaiming EXPIRED extents first. You have to patch your database due to an Oracle bug 5442919.

User UNDO Tablespace Usage

Let's drill down a little bit to user-level.

SQL> select u.tablespace_name tablespace, s.username, u.status, sum(u.bytes)/1024/1024 sum_in_mb, count(u.segment_name) seg_cnts from dba_undo_extents u left join v$transaction t on u.segment_name = '_SYSSMU' || t.xidusn || '$' left join v$session s on t.addr = s.taddr group by u.tablespace_name, s.username, u.status order by 1,2,3;

TABLESPACE           USERNAME        STATUS     SUM_IN_MB   SEG_CNTS
-------------------- --------------- --------- ---------- ----------
UNDOTBS1             SCOTT           ACTIVE          8.00          1
UNDOTBS1             SCOTT           EXPIRED       120.12         66
UNDOTBS1                             EXPIRED      5476.18       1962
UNDOTBS1                             UNEXPIRED     305.40         85
UNDOTBS2                             EXPIRED      1743.67       3252

It's pretty easy to identify undo tablespace usage and who is using undo space just now.

In my experience, DML statements for data migration are the first target we should focused on in case UNDO space is full. DDL like SHRINK, MOVE or REBUILD would also take a significant active undo at runtime.

The second purpose of using UNDO is for snapshot query. I think you may like to know why changing UNDO_RETENTION is usually useless to resolve ORA-01555.

2 thoughts on “Oracle Undo Tablespace Usage”

  1. UNDO:
    ======
    col allocated for 999,999.999
    col free for 999,999.999
    col used for 999,999.999

    select
    ( select sum(bytes)/1024/1024 from dba_data_files
    where tablespace_name like ‘%UND%’ ) allocated_MB,
    ( select sum(bytes)/1024/1024/1024 from dba_free_space
    where tablespace_name like ‘%UND%’) free_GB,
    ( select sum(bytes)/1024/1024/1024 from dba_undo_extents
    where tablespace_name like ‘%UND%’) Used_GB
    from dual
    /

    UNDO BY SESSION LEVE WISE:
    ===========================================================

    select s.sid,s.status,
    s.username,
    sum(ss.value) / 1024 / 1024 as undo_size_mb
    from v$sesstat ss
    join v$session s on s.sid = ss.sid
    join v$statname stat on stat.statistic# = ss.statistic#
    where stat.name = ‘undo change vector size’
    and s.type ‘BACKGROUND’
    and s.username IS NOT NULL
    group by s.sid,s.username,s.status;

Leave a Reply

Your email address will not be published. Required fields are marked *