Undo Tablespace Monitoring

 
UNDO tablespace is special kind of tablespace in Oracle. It can grow and shrink on its own, much like the TEMPORARY tablespace. But unlike a TEMPORARY tablespace, the UNDO tablespace’s datafiles need to be online for the database to open and a corrupted/missing UNDO datafile will crash the database instance.

UNDO tablespace is used by Oracle for the following reasons: Read consistency, Rollback and Flashback.

Read consistency – If you have a query (select statement) that started at 9:00am and sometime around 9:30am the table where your select statement is accessing was updated. It doesn’t matter if the update was committed or uncommitted, you would still get the original data before it was updated if your query was still running at that time (e.g. ended at 10:00am).

Rollback – For rolling back uncommitted data.

Flashback – For rolling back committed data and several other flashback features.

The duration of UNDO data stored in the UNDO tablespace is based on the database parameter UNDO_RETENTION. Default is 900, this paremeter is in seconds.

UNDO extents come in Three different types:

ACTIVE – Extents that are still being used by active transactions running on the database. These are important to monitor because an undo tablespace that is almost full of ACTIVE extents will cause transactions to fail.

UNEXPIRED – Extents whose transactions have already ended become inactive. These are unexpired because these are still within the UNDO_RETENTION parameter (became inactive in less than 900 seconds).

EXPIRED – Extents whose transactions are done and have exceeded the UNDO_RETENTION parameter.

If your UNDO tablespace is set to NOGUARANTEE mode, then the space occupied by UNEXPIRED extents can be reclaimed anytime. This means, in a NOGUARANTEE UNDO tablespace, only ACTIVE extents are monitored.

If your UNDO tablespace is set to GUARANTEE mode, then the space occupied by UNEXPIRED extents CANNOT be reclaimed until the extent has become EXPIRED (past UNDO_RETENTION time). This means, in a GUARANTEE UNDO tablespace, ACTIVE and UNEXPIRED extents are monitored because they can fill up the UNDO tablespace.

Here’s a script I use to monitor undo utilization:

10g:

select undo_ext.tablespace_name,
tblsp.retention,
sum(undo_ext.bytes)/1024/1024 as MB,
undo_ext.status
from dba_undo_extents undo_ext,
dba_tablespaces tblsp
where tblsp.tablespace_name = undo_ext.tablespace_name
group by undo_ext.tablespace_name,
undo_ext.status,
tblsp.retention;

9i:

select undo_ext.tablespace_name,
sum(undo_ext.bytes)/1024/1024 as MB,
undo_ext.status
from dba_undo_extents undo_ext,
dba_tablespaces tblsp
where tblsp.tablespace_name = undo_ext.tablespace_name
group by undo_ext.tablespace_name,
undo_ext.status;

If you need more help with database issues, feel free to send me an email (jimmycdo@yahoo.com) and I'll gladly help.

About Jimbart