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;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/11411056/viewspace-734338/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/11411056/viewspace-734338/