• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

How to monitor Undo Tablespace Usage and the Free Space in Oracle Database

by admin

This post provides the various queries that can be used to monitor Undo space usage. The Undo Space once allocated won’t be deallocated to the OS, by default. But the space can be reused by other transactions once the UNDO_RETENION (or TUNED_UNDORETENTION period) is met.

1. To check the current size of the Undo tablespace:

select sum(a.bytes) as undo_size from v$datafile a, v$tablespace b, dba_tablespaces c where c.contents = 'UNDO' and c.status = 'ONLINE' and b.name = c.tablespace_name and a.ts# = b.ts#;

2. To check the free space (unallocated) space within Undo tablespace:

select sum(bytes)/1024/1024 "mb" from dba_free_space where tablespace_name ='[undo tablespace name]';

3.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;

4. 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;

Alternatively, below one SQL can be used as well:

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);

Filed Under: oracle

Some more articles you might also be interested in …

  1. How to Drop/Truncate Multiple Partitions in Oracle 12C
  2. Oracle Software Group Accounts OSDBA, OSOPER, Oracle Inventory group
  3. RMAN: SET NEWNAME Command Using SQL
  4. Oracle Scheduler 12c New Features and Enhancements
  5. How to move ASM spfile from External Redundancy To Normal Redundancy in version 12.1.0.2 and above
  6. How do we Set A Retention Policy For Tape Backups And Disk Backups Differently
  7. Empty Directories in the Flash Recovery Area (FRA) are not deleted
  8. How to set udev rule for setting the disk permission on ASM disks when using multipath on CentOS/RHEL 6
  9. How to Trace Dynamic Registration from PMON or LREG (Oracle 12c)
  10. Error: ORA-16810: multiple errors or warnings detected for the database

You May Also Like

Primary Sidebar

Recent Posts

  • qm Command Examples in Linux
  • qm wait Command Examples in Linux
  • qm start Command Examples in Linux
  • qm snapshot Command Examples in Linux

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright