UNDO BLOCK
2 min readSpace 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
