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 undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     800
undo_tablespace                      string      THINKTBS
另开一个会话：
hr@ORCL> select count(*) from t;

  COUNT(*)
----------
    462140

hr@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         19
37A19E88          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.77333333
sys@ORCL> show parameter db_block_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     8192
sys@ORCL> show parameter undo_retention

NAME                                 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.