檢視Undo使用情況的SQL

Undo整體的使用狀況：

SELECT
seg.tablespace_name "Tablespace Name",
ts.bytes/1024/1024 "TS Size(MB)",   
ue.status "UNDO Status",
count(*)  "Used Extents",   
round(sum(ue.bytes)/1024/1024, 2) "Used Size(MB)",   
round(sum(ue.bytes)/ts.bytes*100, 2) "Used Rate(%)"
FROM dba_segments seg, DBA_UNDO_EXTENTS ue,   
(SELECT tablespace_name, sum(bytes) bytes     
   FROM dba_data_files GROUPBY tablespace_name) ts
    WHERE ue.segment_NAME=seg.segment_NAME and seg.tablespace_name=ts.tablespace_name
    GROUPBY seg.tablespace_name, ts.bytes, ue.status ORDERBY seg.tablespace_name;

 

每個會話的Undo使用情況：

SELECT s.USERNAME,s.SID,s.SERIAL#,t.UBAFIL "UBA filenum",  t.UBABLK
  "UBA Block number",t.USED_UBLK "Number os undo Blocks Used",
  t.START_TIME,t.STATUS,t.START_SCNB,t.XIDUSN RollID,r.NAME RollName
   FROM v$session s,v$transaction t,v$rollname r
   WHERE s.SADDR=t.SES_ADDR  AND t.XIDUSN=r.usn;

類別：dba指令碼 檢視評論

Link URL: http://hi.baidu.com/ljm0211/blog/item/fbea960adb933f0394ca6bbc.html