undo系列学习之如何计算undo表空间的大小

undo空间的大小不足会引起ORA-30036或者ORA-01555。

我们创建一个小的undo表空间，然后执行一个比较大的事务，这时就可以模拟出ORA-30036.

sys@ORCL> create undo tablespace thinktbs datafile '/u01/app/oracle/oradata/ORCL/datafile/think_undo.dbf'  2  size 2m autoextend off;Tablespace created.sys@ORCL> alter system set undo_tablespace=thinktbs;System altered.sys@ORCL> show parameter undoNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------undo_management                      string      AUTOundo_retention                       integer     800undo_tablespace                      string      THINKTBS另开一个会话：hr@ORCL> select count(*) from t;  COUNT(*)----------    462140hr@ORCL> begin  2        for i in 1..1000  3       loop  4         delete from t where rownum<1001;  5         commit;  6       end loop;  7      end;  8  /begin*ERROR at line 1:ORA-30036: unable to extend segment by 8 in undo tablespace 'THINKTBS'ORA-06512: at line 4在执行这个事务的过程中，我们可以查询它一共使用了多少个undo数据块：sys@ORCL> select addr,used_ublk from v$transaction;ADDR      USED_UBLK-------- ----------37A19440         1937A19E88          1

那么我们该如何确定undo表空间的大小呢？
undo表空间的大小有三个影响因素：
1）undo_retention：这个值的确定，请参见我的blog：http://blog.csdn.net/linwaterbin/article/details/7800190
2）每秒需要的undo数据块：这个值我们可以从v$undostat里面查询，计算其最大值或者平均值都可以，但建议取其最大
3）缺省块大小
公式
undo size=db_block_size*undo_retention*max(undoblks/((end_time-begin_time)*24*3600))

sys@ORCL> select max(undoblks/((end_time-begin_time)*24*3600)) from v$undostat;MAX(UNDOBLKS/((END_TIME-BEGIN_TIME)*24*3600))---------------------------------------------                                   5.77333333sys@ORCL> show parameter db_block_sizeNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------db_block_size                        integer     8192sys@ORCL> show parameter undo_retentionNAME                                 TYPE        VALUE------------------------------------ ----------- ------------------------------undo_retention                       integer     800

通过上面的查询我们就可以计算出undo表空间的大小的具体值。我们便可以相应的增加undo表空间的大小。

sys@ORCL> alter tablespace undotbs1 add datafile '/u01/app/oracle/oradata/ORCL/datafile/thinkundo.dbf' size 30m autoextend on;Tablespace altered.