To see what is currently using the UNDO tablespace in Oracle, you can use the following queries. These will help you analyze active undo transactions, undo tablespace usage, and long-running queries consuming undo.
1. Check Current Undo Tablespace Usage
This query provides an overview of undo tablespace usage, including allocated and used space.
SELECT a.tablespace_name, a.file_name, a.bytes/1024/1024 AS total_size_mb, f.bytes/1024/1024 AS free_space_mb, (a.bytes - f.bytes)/1024/1024 AS used_space_mbFROM dba_data_files aLEFT JOIN dba_free_space f ON a.file_id = f.file_idWHERE a.tablespace_name = (SELECT value FROM v$parameter WHERE name = 'undo_tablespace');🔹 Key Info:
total_size_mb: Total undo tablespace size
free_space_mb: Available free space
used_space_mb: Currently used undo space
2. Find Active Transactions Using Undo
Use this query to list active transactions consuming undo:
SELECT s.sid, s.serial#, s.username, t.used_ublk AS undo_blocks, t.used_urec AS undo_records, t.start_time, s.status, s.program FROM v$transaction t JOIN v$session s ON t.ses_addr = s.saddr ORDER BY t.used_ublk DESC;🔹 Key Info:
sid, serial#: Session details
username: Oracle user running the transaction
undo_blocks: Number of undo blocks used
undo_records: Number of undo records used
start_time: When the transaction began
status: Whether the session is active or inactive
3. Identify Long-Running Queries Using Undo
To detect long-running queries that might be causing ORA-01555 (Snapshot Too Old) errors:
SELECT s.sid, s.serial#, s.username, q.sql_text, t.used_ublk AS undo_blocks, t.start_time FROM v$transaction tJOIN v$session s ON t.ses_addr = s.saddrJOIN v$sql q ON s.sql_id = q.sql_idORDER BY t.used_ublk DESC;🔹 Key Info:
sql_text: The actual SQL query consuming undo space
undo_blocks: Undo blocks used by this query
start_time: When the transaction started
4. Check Undo Tablespace History
This helps you see historical undo usage trends:
SELECT begin_time, end_time, undoblks, txncount, maxquerylen FROM v$undostat ORDER BY begin_time DESC;🔹 Key Info:
undoblks: Undo blocks used during this period
txncount: Number of transactions
maxquerylen: Longest query duration (in seconds)
5. Find the Largest Undo-Consuming Queries
SELECT s.sid, s.serial#, q.sql_text, t.used_ublk AS undo_blocks FROM v$transaction tJOIN v$session s ON t.ses_addr = s.saddrJOIN v$sql q ON s.sql_id = q.sql_idORDER BY t.used_ublk DESC FETCH FIRST 5 ROWS ONLY;🔹 Key Info:
Finds the top 5 sessions consuming the most undo space.
Conclusion
If undo space is full, you may need to increase UNDO tablespace size.
If long queries are running, optimize them using indexes or parallel execution.
If UNDO is being overwritten too soon, enable RETENTION GUARANTEE.
Let me know if you need further assistance! 🚀