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

The Geek Diary

CONCEPTS | BASICS | HOWTO

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • Linux Services
    • VCS
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
    • Data Guard
  • DevOps
    • Docker
    • Shell Scripting
  • Interview Questions
  • 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 Configure Device File owner/group with udev rules
  2. How to Install and configure OSWatcher Black Box (OSWbb)
  3. How to Create Interval-Reference Partitioned Tables in Oracle 12c
  4. How to Create Undo Tablespace for a Newly Added RAC Instance (ORA-30012)
  5. Oracle RMAN 11g New Feature – MultiSection Backups
  6. Oracle ASM : Shell script to map physical disk devices to ASMLIB disks
  7. Oracle Database : script to create a “CREATE SYNONYM Script”
  8. How to Migrate ASM Disk Groups to another Storage Online [non ASMLIB Devices]
  9. Recommendation for the Oracle Real Application Cluster Interconnect and Jumbo Frames
  10. Script to monitor RMAN Backup and Restore Operations

You May Also Like

Primary Sidebar

Recent Posts

  • How to disable ACPI in CentOS/RHEL 7
  • How to Use real-time query to access data on a physical standby database
  • CentOS/RHEL 8: “ACPI MEMORY OR I/O RESET_REG” Server Hung after reboot
  • How to Create a Physical Standby Database by Using SQL and RMAN Commands
  • Archives
  • Contact Us
  • Copyright

© 2021 · The Geek Diary