From dba_rollback_segs r, v$rollstat v,v$transaction t,v$session s
Where r.segment_id = v.usn and v.usn=t.xidusn and t.addr=s.taddr
order by segment_name ;
col UNDO_RETENTION for a15
col DB_BLOCK_SIZE for a15
select ur undo_retention, dbs db_block_size, ((ur * (ups * dbs))+(dbs * 24))/1024/1024 as "M_bytes"
from (select value as ur from v$parameter where name = 'undo_retention'),
(select (sum(undoblks) / sum(((end_time - begin_time) * 86400))) ups from v$undostat),
(select value as dbs from v$parameter where name = 'db_block_size');
col UNDO_RETENTION for a15
col DB_BLOCK_SIZE for a15
select ur undo_retention, dbs db_block_size, ((ur * (ups * dbs)) + (dbs * 24)) / 1024 / 1024 as "M_bytes"
from (select value as ur from v$parameter where name = 'undo_retention'),
(select (undoblks / ((end_time - begin_time) * 86400)) ups from v$undostat where undoblks in (select max(undoblks) from v$undostat)),
(select value as dbs from v$parameter where name = 'db_block_size');
UNDO 中各种extent 的情况
select sum(bytes)/1024/1024 MB, status, tablespace_name
from dba_undo_extents
group by status, tablespace_name order by 3, 2;
查看undo segemnts 大小 dba_segments
col SEGMENT_NAME for a30
col OWNER for a8
col TABLESPACE_NAME for a20
select tablespace_name, owner, segment_name, bytes/1024/1024 mb
from dba_segments where tablespace_name like 'UNDOTBS%';
查看undo segemnts 大小 v$rollstat
select segment_name, v.rssize/1024/1024 mb
From dba_rollback_segs r, v$rollstat v
Where r.segment_id = v.usn(+)
order by segment_name ;
undo segemnt extent info
(initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent,
max_extents, v.curext CurExtent
From dba_rollback_segs r, v$rollstat v
Where r.segment_id = v.usn(+)
order by segment_name ;
查看某个事务的UNDO 情况
select s.sid, s.serial#, t.XIDUSN, t.STATUS, t.USED_UBLK
from v$transaction t, v$session s
where t.addr = s.taddr;
查看某个事务的UNDO 详细情况
set lines 199
col STATUS for a8
col USERNAME for a6
col name for a25
col substr(s.program,1,78) for a30
SELECT r.name ,
d.tablespace_name,
s.sid,
s.serial#,
s.username,
t.status,
t.cr_get,
t.phy_io,
t.used_ublk,
t.noundo,
substr(s.program,1,78)
FROM sys.v_$session s,sys.v_$transaction t,sys.v_$rollname r,Dba_Rollback_Segs d
WHERE t.addr=s.taddr
and t.xidusn=r.usn
AND d.segment_name= r.name
ORDER BY t.cr_get,t.phy_io;