Undo tablespace management.

I have 3 UNDO tablespaces

TableSpace      UsedMB            FreeMBG            TotalMB            %Free
UNDOTBS1      23765            8760            32525            27
UNDOTBS2      12240            48            12288            0
UNDOTBS3      4260            768            5028            15
 

UNDOTBS2 is full.

1. Will a transaction that started in UNDOTBS2 move automatically to UNDOTBS1 or UNDOTBS3
2. How do i identify which program or script is running which is filling up all UNDO tablespace.

gram77Asked:
Who is Participating?
 
OP_ZaharinConnect With a Mentor Commented:
- the undo space will go to 100% until it meets your undo retention settings. and then it will  
start re-use expired or unexpired extents within the undo itself. asktom explain the undo management in detail here: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6894817116500
0
 
OP_ZaharinConnect With a Mentor Commented:
1- it will switch automatically if UNDO_MANAGEMENT = AUTO. check the parameter setting:
SELECT name, value 
FROM gv$parameter
WHERE name LIKE '%undo%';

Open in new window

- you can set the undo tablespace to a new one manually (if the UNDO_MANAGEMENT = MANUAL) by
 running the following alter statement:
ALTER SYSTEM SET UNDO_TABLESPACE = 'UNDOTBS3';

Open in new window


2- run the following query to see which user or program is using the undo tablespace:
SELECT TO_CHAR(s.sid)||','||TO_CHAR(s.serial#) sid_serial,
           NVL(s.username, 'None') orauser,
           s.program,
           r.name undoseg,
           t.used_ublk * TO_NUMBER(x.value)/1024||'K' "Undo"
      FROM sys.v_$rollname    r,
           sys.v_$session     s,
           sys.v_$transaction t,
           sys.v_$parameter   x
    WHERE s.taddr = t.addr
      AND r.usn   = t.xidusn(+)
     AND x.name  = 'db_block_size';

Open in new window

- OR run the folowing query againts session and transaction view. used_ublk and used_urec columns contain the number of undo blocks and undo records by the transaction:
SELECT A.SID, A.USERNAME, B.XIDUSN, B.USED_UREC, B.USED_UBLK   
 FROM V$SESSION A, V$TRANSACTION B   
 WHERE A.SADDR=B.SES_ADDR;  

Open in new window

- you can also use the SID to query to v$sql to see if the script can be found there. replace 'x' with the sid:
  SELECT sql_text, sid
 FROM v$session a,
       v$sql b
 WHERE b.address = a.sql_address
   AND b.hash_value = a.sql_hash_value
   AND a.sid=x;

Open in new window

 
0
 
OP_ZaharinConnect With a Mentor Commented:
- i think i lead you to a wrong answer on #1. it will not switch automatically. you need to switch it manually. the syntax is given earlier.

- if the active undo tablespace runs out of space, you can add files to it or resize the existing data files and specify autoextend so Oracle will extend the datafile should it reach full:
ALTER TABLESPACE UNDOTBS2  ADD DATAFILE 'E:\ORACLE\DATA\PROD2\DATAFILEUNDOTBS2b.DBF' SIZE 1000M  AUTOEXTEND ON;
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
gram77Author Commented:
v$transaction table is empty. All other tables are populated
Therefore all the below queries return no rows.

SELECT TO_CHAR(s.sid)||','||TO_CHAR(s.serial#) sid_serial,
           NVL(s.username, 'None') orauser,
           s.program,
           r.name undoseg,
           t.used_ublk * TO_NUMBER(x.value)/1024||'K' "Undo"
      FROM sys.v_$rollname    r,
           sys.v_$session     s,
           sys.v_$transaction t,
           sys.v_$parameter   x
    WHERE s.taddr = t.addr
      AND r.usn   = t.xidusn(+)
     AND x.name  = 'db_block_size';



SELECT A.SID, A.USERNAME, B.XIDUSN, B.USED_UREC, B.USED_UBLK  
 FROM V$SESSION A, V$TRANSACTION B  
 WHERE A.SADDR=B.SES_ADDR;  


SELECT sql_text, sid
 FROM v$session a,
       v$sql b
 WHERE b.address = a.sql_address
   AND b.hash_value = a.sql_hash_value
   AND a.sid=x;
0
 
OP_ZaharinConnect With a Mentor Commented:
- used_ublk column is use to track undo by session and v$transaction lists only the active transactions. if no transaction is running, it will not return any records. i'm suggesting that you add a new datafile to the UNDOTBS2  and sets the datafile to extend automatically.
0
 
gram77Author Commented:
If no active transaction is running then why is the space 100% consumed. The transaction that ended should have automatically reclaimed space from UNDOTBS2
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.