5 Replies Latest reply: Sep 13, 2013 6:41 AM by JohnWatson RSS

    Someone eating my UNDO tbs

    DOA

      Hello all,

       

      I have 11gr2 with r12.

       

      we have 60GB undo tbs, retention is 900s set.

       

      since last 2 days our undo is showing 98% used, no recent changes done.

      I googled, lots of sql's are there to know what is consuming UNDO, but the list what I am getting as an output result are in KB's.

       

       

      for example:

      select * from V$SYSMETRIC_HISTORY;

       

      select

      s.username 

      ,s.sid 

      ,s.serial# 

      ,s.osuser 

      ,s.logon_time 

      ,s.status 

      ,s.machine 

      ,t.used_ublk 

      ,t.used_ublk*8192/1024 undo_usage_kb 

      from v$session     s 

          ,v$transaction t 

      where t.addr = s.taddr;

       

      above query is giving me num of sessions but all are in kb's, then where is undo been consumed.

       

      Please help me to find out the culprit.

       

      Thanks in ADV