IT博客汇 | How to monitor Undo Tablespace Usage and the Free Space (文档 ID 1951400.1)

How to monitor Undo Tablespace Usage and the Free Space (文档 ID 1951400.1)

admin发表于 2015-05-08 01:52:46

Applies to:

Oracle Database – Enterprise Edition – Version 9.2.0.1 and later
Information in this document applies to any platform.

Symptoms

This document provides the various queries that can be used to monitor Undo space usage.

The Undo Space once allocated wont 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.

Cause

None

Solution

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 ='';

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;