查看undo tablespace空间的大小

SELECT (rd * (ups * overhead) + overhead) AS "Bytes"
  FROM (SELECT VALUE AS rd FROM v$parameter WHERE NAME = 'undo_retention'),
       (SELECT (SUM(undoblks) / SUM(((end_time - begin_time) * 86400))) AS ups FROM v$undostat),
       (SELECT VALUE AS overhead FROM v$parameter WHERE NAME = 'db_block_size');

这个sql得到的是undo表空间的要求的空间大小。 可以用这个查询的结果来设置undo_retention参数和undo表空间的大小

 

 

Undo Space = (UNDO_RETENTION * (Undo Blocks Per Second *     DB_BLOCK_SIZE) ) + DB_BLOCK_SIZE