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') |
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') |
select sum(bytes / 1024 / 1024), status, tablespace_name from dba_undo_extents group by status, tablespace_name; |
该查询将返回以STATUS分组的各状态回滚信息所使用的空间量,一般存在三种STATUS状态:EXPIRED,UNEXPIRED,ACTIVE。ACTIVE表示目前仍活跃的事务相关回滚信息,UNEXPIRED表示虽然事务已经结束但回滚信息保留的时间仍未超过实例参数UNDO_RETENTION所设定的值,EXPIRED表示回滚信息保留时间已超过UNDO_RETENTION所设定的值。 在UNDO表空间未启用guarantee选项的情况下(当前使用情况),新事务的回滚空间分配遵循以下依据: a
本文转自maclean_007 51CTO博客,原文链接:http://blog.51cto.com/maclean/1276810
)