SoFunction
Updated on 2025-04-13

Check the usage of UNDO tablespaces in Oracle database (Latest recommendation)

In Oracle database, you can check different data dictionary views to view the usage of UNDO tablespaces. The following is a detailed introduction to several common viewing methods.

1. View basic information through DBA_TABLESPACES and DBA_DATA_FILES

These two views can provide basic information about the UNDO tablespace, such as the name, status, and the location and size of the data file.

-- Query UNDO Basic information about tablespaces
SELECT ts.tablespace_name, , df.file_name,  / 1024 / 1024 AS "Size (MB)"
FROM dba_tablespaces ts
JOIN dba_data_files df ON ts.tablespace_name = df.tablespace_name
WHERE  = 'UNDO';

Code explanation:

  • dba_tablespacesThe view stores metadata for all tablespaces.
  • dba_data_filesThe view contains detailed information of all data files.
  • passJOINOperations associate two views and filter outcontentsforUNDOtablespace information.
  • bytes / 1024 / 1024Convert bytes to megabytes.

2. View UNDO usage statistics through V$UNDOSTAT

V$UNDOSTATThe view can provide statistics on the usage of UNDO tablespace, such as the amount of UNDO generated, the number of active UNDO blocks, etc.

-- Query UNDO Usage statistics
SELECT 
    usn, 
    rssize / 1024 / 1024 AS "Undo Segment Size (MB)", 
    wrcount AS "Write Count", 
    optcnt AS "Optimal Count", 
    expcnt AS "Expired Count", 
    unexpcnt AS "Unexpired Count"
FROM v$undostat;

Code explanation:

  • usnis the number of the UNDO segment.
  • rssizeis the size of the UNDO segment, converted to megabyte display.
  • wrcountIt is the number of write operations.
  • optcntIt is the optimal number of uses.
  • expcntis the number of expired UNDO blocks.
  • unexpcntis the number of UNDO blocks that have not expired.

3. View the free space of UNDO tablespace through DBA_FREE_SPACE

DBA_FREE_SPACEViews can help you understand how much free space is left in the UNDO tablespace.

-- Query UNDO Table space free space
SELECT 
    tablespace_name, 
    SUM(bytes) / 1024 / 1024 AS "Free Space (MB)"
FROM dba_free_space
WHERE tablespace_name IN (SELECT tablespace_name FROM dba_tablespaces WHERE contents = 'UNDO')
GROUP BY tablespace_name;

Code explanation:

  • First fromdba_tablespacesFilter out from the viewcontentsforUNDOThe tablespace name.
  • Then indba_free_spaceIn the view, query based on these tablespace names and sum the free space.
  • Finally convert the result to megabyte display.

4. Check the UNDO situation occupied by the currently active transaction through V$TRANSACTION

V$TRANSACTIONThe view can display the currently active transactions and the UNDO resources they occupy.

-- Query the currently occupied by active transactions UNDO Condition
SELECT 
    , 
    #, 
    t.used_ublk, 
    t.start_time
FROM v$session s
JOIN v$transaction t ON  = 
WHERE  = 'ACTIVE';

Here is the article on how to check the usage of UNDO tablespaces in Oracle databases? That’s all for the article. For more related Oracle UNDO tablespace content, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!