What is UNDO?
- Every Oracle Database requires a mechanism to manage information used to reverse or “undo” changes made to the database. This information consists of records of actions performed by transactions, primarily before they are committed. These records are collectively called “undo.”
- Undo records are used to reverse transactions when a ROLLBACK statement is issued, Assist in database recovery, Ensure read consistency
- When a ROLLBACK statement is executed, undo records revert changes made by the uncommitted transaction. During database recovery, undo records undo any uncommitted changes that were applied from the redo log to the data files. Additionally, undo records help maintain read consistency by preserving the original data for users accessing it while others are modifying it.
How to monitor the usage of UNDO tablespace
(1)Check allocated space and free space for UNDO tablespace.
SELECT /* + RULE */ df.tablespace_name "Tablespace",
df.bytes / (1024 * 1024) "Size (MB)",
SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
FROM dba_free_space fs,
(SELECT tablespace_name,SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
fs.bytes / (1024 * 1024),
SUM(df.bytes_free) / (1024 * 1024),
Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
FROM dba_temp_files fs,
(SELECT tablespace_name,bytes_free,bytes_used
FROM v$temp_space_header
GROUP BY tablespace_name,bytes_free,bytes_used) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
ORDER BY 4 DESC;
(2)To Check the space available within the allocated Undo tablespace
select tablespace_name , sum(blocks)*8/(1024) reusable_space from dba_undo_extents where status='EXPIRED' group by tablespace_name;
(3)To Check the space allocated in the Undo tablespace:
select tablespace_name , sum(blocks)*8/(1024) space_in_use from dba_undo_extents where status IN ('ACTIVE','UNEXPIRED') group by tablespace_name;
You can use below combined SQL for the above
with free_sz as ( select tablespace_name, sum(f.bytes)/1048576/1024 free_gb from dba_free_space f group by tablespace_name ) , a as ( select tablespace_name , sum(case when status = 'EXPIRED' then blocks end)*8/1048576 reusable_space_gb , sum(case when status in ('ACTIVE', 'UNEXPIRED') then blocks end)*8/1048576 allocated_gb from dba_undo_extents where status in ('ACTIVE', 'EXPIRED', 'UNEXPIRED') group by tablespace_name ) , undo_sz as ( select tablespace_name, df.user_bytes/1048576/1024 user_sz_gb from dba_tablespaces ts join dba_data_files df using (tablespace_name) where ts.contents = 'UNDO' and ts.status = 'ONLINE' ) select tablespace_name, user_sz_gb, free_gb, reusable_space_gb, allocated_gb , free_gb + reusable_space_gb + allocated_gb total from undo_sz join free_sz using (tablespace_name) join a using (tablespace_name) ;
Here
ACTIVE: Indicates active transactions in the database that are using the undo tablespace. These cannot be truncated.
EXPIRED: Represents transactions that have been completed and have surpassed the undo retention time, making them the first candidates for truncation from the undo tablespace.
UNEXPIRED: Refers to completed transactions that have not yet reached the undo retention time. They can be truncated if necessary.
Therefore The sum of free space in (1) and space allocated in (2) correctly specifies the free space available for transactions. You can use that as a metric to add space in the UNDO tablespace
Related Articles
How to check tablespace in Oracle
How to check temp tablespace in Oracle
How to add datafile to tablespace in Oracle
How to check default tablespace