Oracle中查看回滾段狀態的腳本

SELECT a.name, b.STATUS
  FROM V$ROLLNAME a, V$ROLLSTAT B
 WHERE NAME IN (select SEGMENT_NAME from dba_segments)
   and a.usn = b.USN

 

 

 

select s.username, u.name
from v$transaction t, v$rollstat r, v$rollname u, v$session s
where s.taddr = t.addr
and t.xidusn = r.usn
and r.usn = u.usn
order by s.username;

 

查看各用戶在利用的回滾段

 

 

 

select ((ur*(ups*dbs))+(dbs*24))/1048576 as "MB"
from (select value as ur from v$parameter where name='undo_retention'),
(select (sum(undoblks)/sum(((end_time-begin_time)*86400))) as ups from v$undostat),
(select value as dbs from v$parameter where name='db_block_size')
利用視圖來優化UNDO空間的空間大小

for best results,the calculation should be made during the time of day when the database has its heaviest workload