How to Check UNDO Tablespace Usage

  • by

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.

Overall 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 SMON detects space pressure, it either autoextend file size or reclaim EXPIRED extents.

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.

Leave a Reply

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