A technical troubleshooting blog about Oracle with other Databases & Cloud Technologies.

UNDO BLOCK

2 min read
a close up shot of a command key

Photo by Darren Halos on Pexels.com

Space allocation in the UNDO tablespace is unique and does not follow the same rules as normal tablespaces. Data within the tablespace frequently grows and shrinks throughout the life of the instance.

There are three categories of blocks in the UNDO tablespace: Active, unexpired and expired

Active: Blocks that contain active UNDO data for active transactions. They are required until the active transaction is committed or rolled-back. Active blocks do not get overwritten.

Unexpired: These blocks were once active, but have been committed. They are retained as unexpired and are required for FLASHBACK_QUERY for the duration of the ‘undo_retention’ parameter (in seconds). They can be overwritten and used for active UNDO if there are no expired blocks to use.

Expired: Once the UNDO_RETENTION period has passed, unexpired blocks are moved into the expired category. These blocks are the first used for active UNDO.

Current UNDO usage by category

View the current configuration of Active, Unexpired and Expired UNDO

set pages 10 
break on tablespace_name skip 2
column TABLESPACE_NAME format a16
column MB format 999,999,999
compute sum of MB on tablespace_name
SELECT tablespace_name, status, Round( sum_bytes / ( 1024 * 1024 ), 0 )    AS MB, Round( ( sum_bytes / mb_total ) * 100, 2 ) AS PERCENT_USED
FROM  ( SELECT status, tablespace_name, SUM( bytes ) sum_bytes
        FROM   DBA_UNDO_EXTENTS
        GROUP  BY status,tablespace_name ),
      ( SELECT SUM( a.bytes ) undo_size
        FROM   DBA_TABLESPACES c,
               V$TABLESPACE b,
               V$DATAFILE a
        WHERE  a.ts# = b.ts# AND
               b.name = c.tablespace_name AND
               c.CONTENTS = 'UNDO' AND
               c.status = 'ONLINE' ),
      ( SELECT SUM( DBA.maxbytes ) AS MB_TOTAL
        FROM   DBA_DATA_FILES DBA,
               V$DATAFILE v
        WHERE  DBA.file_id = v.file# AND
               DBA.tablespace_name =
               ( SELECT value
                 FROM   V$PARAMETER
                 WHERE  name = 'undo_tablespace' ) ),
      ( SELECT maxquerylen
        FROM   V$UNDOSTAT
        WHERE  ROWNUM = 1 )
WHERE  tablespace_name =
       ( SELECT value
         FROM   V$PARAMETER
         WHERE  name = 'undo_tablespace' )
ORDER  BY 1,2;
 
Example:
  
    TABLESPACE_NAME  STATUS              MB PERCENT_USED
    ---------------- --------- ------------ ------------
    UNDOTBS1         ACTIVE         984,143        23.32
                             EXPIRED          9,986          2.98
                             UNEXPIRED      89,54         11.90
    ****************              ------------
    sum                                   ***,***

DB_UNDO_GB_USAGE 

with db_blck_size as(
select /*+ MATERIALIZE */ value from v$parameter
WHERE NAME = 'db_block_size'
),
trans as (
select /*+ MATERIALIZE */ * FROM v$transaction
),
sess as (
SELECT /*+ MATERIALIZE */ * FROM v$session
),
temp as (
select /*+ MATERIALIZE */ s.sid, s.serial#,
s.client_info, t.addr,
sum(t.used_ublk) num_used_undoblk
from trans t inner join sess s on t.addr = s.taddr
group by s.sid, s.serial#, s.client_info, t.addr
order by 5 desc
)
select u.sid, u.serial# as serial_number, u.client_info,
CAST(u.addr as varchar2(20)) addr, u.num_used_undoblk,
(p.value*u.num_used_undoblk)/1024/1024 as used_undoblk_mb,
(p.value*u.num_used_undoblk)/1024/1024/1024 as used_undoblk_gb