如何设置撤销表空间大小

一.在高峰时查询v$undostat
SQL> select end_time,begin_time,(end_time-begin_time)*24*60*60 interval,undoblks
from v$undostat;

END_TIME BEGIN_TIME INTERVAL UNDOBLKS
-------------- -------------- ---------- ----------
05-17 22:46:51 05-17 22:44:00 171 259
05-17 22:44:00 05-17 22:34:00 600 313
05-17 22:34:00 05-17 22:24:00 600 2
05-17 22:24:00 05-17 22:14:00 600 386
05-17 22:14:00 05-17 22:04:00 600 618
05-17 22:04:00 05-17 21:54:00 600 52

已选择6行。

算出每秒钟产生多少个undo块:
SQL> select sum(undoblks)/sum((end_time-begin_time)*24*60*60) undo_blks_per_seco
nds from v$undostat;

UNDO_BLKS_PER_SECONDS
---------------------
.41041041

二.应用公式:
UndoSize=Undo_Retention * Undo blocks per second * block size * (1 + overhead)

所以
UndoSize = 900 * 0.4104 * 8192 /1024/1024 * (1 + 10%)
= 3.174M

alter system reset sql_trace scope=spfile sid='*';