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表空间的大小呢?可以借助EM来管理。
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.