How to deal with UNDO space usage alert


How to deal with UNDO space usage alert

Oracle Database creates and manages information that is used to roll back, or undo, changes to the database. Such information consists of records of the actions of transactions, primarily before they are committed. These records are collectively referred to as undo.

Undo records are used to:
  • Roll back transactions when a ROLLBACK statement is issued
  • Recover the database
  • Provide read consistency
  • Analyze data as of an earlier point in time by using Oracle Flashback Query
  • Recover from logical corruptions using Oracle Flashback features
When a ROLLBACK statement is issued, undo records are used to undo changes that were made to the database by the uncommitted transaction. During database recovery, undo records are used to undo any uncommitted changes applied from the redo log to the datafiles. Undo records provide read consistency by maintaining the before image of the data for users who are accessing the data at the same time that another user is changing it.

Whenever you get an UNDO space usage alert, do not go for immediately increasing the UNDO tablespace size. Check how many expired extents and free extents are left in the undo tablespace. These extents can be reused and hence even though if the undo tablespace shows near 100% utilized, you don’t need to add space to the tablespace as there are segments to get reused. If the sum of (expired extent + free space) is low or you are getting ORA-1555 Snapshot too old error (in the alert log) then you need to think about it and go for increasing space in undo tablespace.

SQL Query for checking UNDO tablespace usage [Free Space]:

set pages 999
col tablespace_name format a40
col "size MB" format 999,999.99
col "free MB" format 999,999.99
col "% Used" format 999.99
select      tsu.tablespace_name, ceil(tsu.used_mb) "size MB"
,     decode(ceil(tsf.free_mb), NULL,0,ceil(tsf.free_mb)) "free MB"
,     decode(100 - ceil(tsf.free_mb/tsu.used_mb*100), NULL, 100,
               100 - ceil(tsf.free_mb/tsu.used_mb*100)) "% Used"
from  (select tablespace_name, sum(bytes)/1024/1024 used_mb
      from dba_data_files group by tablespace_name union all
      select      tablespace_name || '  **TEMP**'
      ,     sum(bytes)/1024/1024 used_mb
      from dba_temp_files group by tablespace_name) tsu
,     (select tablespace_name, sum(bytes)/1024/1024 free_mb
      from dba_free_space group by tablespace_name) tsf
where tsu.tablespace_name = tsf.tablespace_name (+)
and tsu.tablespace_name = 'APPS_UNDOTS1'
order by 4
/


[Specify the name of Undo Tablespace that you are using.]


SQL Query for checking UNDO extent(s) status [Expired Extents]:

set linesize 200 pagesize 200
select tablespace_name, status segment_status,
count(extent_id) "Extent Count", sum(blocks) "Total Blocks",
sum(blocks)*8/(1024) "Total Space in MB"
from dba_undo_extents
group by tablespace_name, status
order by tablespace_name
/

TABLESPACE_NAME                SEGMENT_S Extent Count Total Blocks Total Space in MB
------------------------------ --------- ------------ ------------ -----------------
APPS_UNDOTS1                   ACTIVE              20         9496           74.1875
APPS_UNDOTS1                   EXPIRED            761       336312         2627.4375
APPS_UNDOTS1                   UNEXPIRED         2513       686448          5362.875

Note:
All the above said are valid only when the undo is in auto extend off.
The best practice is to have it in autoextend off.

Comments

Back To Top

Popular posts from this blog

error 18 at 0 depth lookup: self signed certificate

How to check fragmentation in MySQL tables

How to Drop or Remove or Decommission a Database in Oracle