Oracle Undo Tablespace management

 

UNDO TABLESPACE

USES:

· Rollback transactions.

· Recover the database.

· Provide read consistency.

How to monitor Undo Tablespace Usage:

In UNDO tablespace once we allocate the size, if that in currently used we cannot use the space in other process

To check the current size of the Undo tablespace:

Find undo tablespace current size in dba_tablespace view check the status as ONLINE to get current usage

SQL> 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#;

 UNDO_SIZE

----------

 775946240

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

The dba_free_space we will find tablespace free size mention the tablespace name to get free size of the tablesapce.

SQL> select sum(bytes)/1024/1024 "mb" from dba_free_space where tablespace_name ='UNDOTBS1';

        mb

----------

  718.4375

 

 

To Check the space available within the allocated Undo tablespace:

Undo tablespace allocated with the database to find that free space available in that use the dba_undo_segments and mention the status as expired to get available space in allocated undo tablesapce

SQL> select tablespace_name, sum(blocks)*8/(1024) reusable_space from dba_undo_extents where status='EXPIRED' group by tablespace_name;

TABLESPACE_NAME                REUSABLE_SPACE

------------------------------ --------------

UNDOTBS1                                 8.25

 

To Check the space allocated in the Undo tablespace:

Check currently space can be used by undo tablespace check the extents sattus as active and unexpired to get the used size

SQL> select tablespace_name , sum(blocks)*8/(1024) space_in_use from dba_undo_extents where status IN ('ACTIVE','UNEXPIRED') group by  tablespace_name;

 

TABLESPACE_NAME                SPACE_IN_USE

------------------------------ ------------

UNDOTBS1                            12.3125

 

Find over all undo tablespace status

Following script used to find overall undo tablesapce size usage and free space

 

SQL> select a.tablespace_name, SIZEMB, USAGEMB, (SIZEMB - USAGEMB) FREEMB

from (select sum(bytes) / 1024 / 1024 SIZEMB, b.tablespace_name

from dba_data_files a, dba_tablespaces b

where a.tablespace_name = b.tablespace_name

and b.contents = 'UNDO'

group by b.tablespace_name) a,

(select c.tablespace_name, sum(bytes) / 1024 / 1024 USAGEMB

from DBA_UNDO_EXTENTS c

where status <> 'EXPIRED'

group by c.tablespace_name) b

where a.tablespace_name = b.tablespace_name;

TABLESPACE_NAME                    SIZEMB    USAGEMB     FREEMB

------------------------------ ---------- ---------- ----------

UNDOTBS1                                 740    12.3125   727.6875

 

SQL>

 

Check undo usage by User or schema

select u.tablespace_name tablespace, s.username, u.status, sum(u.bytes)/1024/1024 sum_in_mb, count(u.segment_name) seg_cnts
from dba_undo_extents u, v$transaction t , v$session s
where u.segment_name = '_SYSSMU' || t.xidusn || '$' and t.addr = s.taddr
group by u.tablespace_name, s.username, u.status order by 1,2,3;

 

If undo tablesapce 100 % full ?

 

Undo tablespace 100 % full we cannot resize the table space size or we cannot add datafile to the tablespace if we add that file size also shown as 100 % full.

We create new tablespace then we assign the tablespace to default undo tablesapce

Once the process will be completed it will release the memory . if we also remove the used tablespace.