本文共 2386 字，大约阅读时间需要 7 分钟。

-- 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/transactionSELECT   (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_textORDER 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

转载地址：http://tjtai.baihongyu.com/