Oracle Undo Space Usage Related Queries

-- show summary active undo tablespace usage  
SELECT   ts.tablespace_name active_undo  
        , SUM (f.BYTES) / 1024 / 1024 total_undo_mb  
        , (SELECT SUM (ext.BYTES) / 1024 / 1024 used_undo_mb  
             FROM dba_undo_extents ext  
            WHERE ext.status IN ('ACTIVE', 'UNEXPIRED')  
              AND ts.tablespace_name = ext.tablespace_name) used_undo_mb  
        , (  SUM (f.BYTES) / 1024 / 1024  
           - (SELECT SUM (ext.BYTES) / 1024 / 1024 used_undo_mb  
                FROM dba_undo_extents ext  
               WHERE ext.status IN ('ACTIVE', 'UNEXPIRED')  
                 AND ts.tablespace_name = ext.tablespace_name)  
          ) free_undo_mb  
    FROM dba_data_files f  
        ,dba_tablespaces ts  
   WHERE f.tablespace_name = ts.tablespace_name  
     AND ts.CONTENTS = 'UNDO'
     AND ts.tablespace_name = (SELECT VALUE  
                                 FROM v$parameter  
                                WHERE LOWER (NAME) = 'undo_tablespace')  
GROUP BY ts.tablespace_name;

-- show undo space used by session/transaction
SELECT   (SELECT VALUE
            FROM v$parameter
           WHERE LOWER (NAME) = 'undo_tablespace') tablespace_name
        ,SUM (  t.used_ublk
              * (SELECT ts.block_size
                   FROM dba_tablespaces ts
                  WHERE ts.CONTENTS = 'UNDO'
                    AND ts.tablespace_name =
                                      (SELECT VALUE
                                         FROM v$parameter
                                        WHERE LOWER (NAME) = 'undo_tablespace'))
              / 1024
              / 1024) mb
        ,se.SID
        ,se.serial#
        ,se.username
        ,se.status
        ,se.osuser
        ,se.machine
        ,se.program
        ,se.action
        ,sq.sql_text
    FROM v$transaction t
        ,v$session se
        ,v$sql sq
   WHERE t.addr = se.taddr
     AND se.sql_address = sq.address(+)
     AND se.sql_hash_value = sq.hash_value(+)
GROUP BY se.SID
        ,se.serial#
        ,se.username
        ,se.status
        ,se.osuser
        ,se.machine
        ,se.program
        ,se.action
        ,sq.sql_text
ORDER BY 2 DESC;


Ref:
1. undo tablespace usage
http://www.orafaq.com/forum/t/121643/2/
2. How to determine undo usage in Oracle
http://blog.mydream.com.hk/howto/how-to-determine-undo-usage-in-oracle
3. UNDO tablespace usage
http://blog.contractoracle.com/2008/08/undo-tablespace-usage.html
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值