UNDO是用于实现并发控制以及构建一致性读，也就是在数据变更之前产生前镜像，以保证用户能够回滚或撤销对数据库所作的修改。是Oracle数据库完整性的重要组成部分。因此合理的设计及配置undo以及使用undo都将对数据库有较大的影响。通常情况下，对于大规模数据的删除，更新操作，我们建议使用分批删除分次提交以减少对undo的占用和冲击。那么对于undo的大小到底应该设置多大？是启用自动扩展还是关闭自动扩展？这个问题仁者见仁，智者见智，见下文。

1、当前数据库环境及undo配置信息

sys@SYTST> select * from v$version where rownum<2;

BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.3.0 - 64bit Production

sys@SYTST> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTBS

sys@SYTST> select tablespace_name,file_name,AUTOEXTENSIBLE,bytes/1024/1024 size_mb
  2  from dba_data_files where tablespace_name like '%UNDO%';

TABLESPACE_NAME                FILE_NAME                                               AUT    SIZE_MB
------------------------------ ------------------------------------------------------- --- ----------
UNDOTBS                        /u02/database/SYTST/undo/undoSYTST1.dbf                 NO          20

--创建演示表t
sys@SYTST> CREATE TABLE t
  2  AS
  3  SELECT rownum AS id,
  4         round(5678+dbms_random.normal*1234) AS n1,
  5         mod(255+trunc(dbms_random.normal*1000),255) AS n2,
  6         dbms_random.string('p',255) AS pad
  7  FROM dual
  8  CONNECT BY level <= 100000
  9  ORDER BY dbms_random.value;

Table created.

--直接用脚本得到undo的信息及建议值
sys@SYTST> @chk_advs_undo

Session altered.


- Undo Analysis started at : 24/10/2013 14:39:58 -
--------------------------------------------------
NOTE:The following analysis is based upon the database workload during the period -
Begin Time : 17/10/2013 14:39:58
End Time   : 24/10/2013 14:39:58

Current Undo Configuration
--------------------------
Current undo tablespace                                 : UNDOTBS
Current undo tablespace size (datafile size now)        : 20M
Current undo tablespace size (consider autoextend)      : 20M
AUTOEXTEND for undo tablespace is                       : OFF
Current undo retention                                  : 900
UNDO GUARANTEE is set to                                : FALSE

Undo Advisor Summary
---------------------------
Finding 1:The undo tablespace is OK.    --->当前的undo配置合理

Undo Space Recommendation
-------------------------
Allocated undo space is sufficient for the current workload.

Retention Recommendation
------------------------
The best possible retention with current configuration is    : 5996 Seconds
The longest running query ran for                            : 52 Seconds
The undo retention required to avoid errors is               : 52 Seconds

PL/SQL procedure successfully completed.

2、模拟undo超出并获得建议值

--先查看当前已产生的undo
sys@SYTST> @mystat "undo change"
sys@SYTST> set echo off

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
undo change vector size                                              363568

--我们来更新之前创建表t上的pad列
sys@SYTST> update t set pad=dbms_random.string('l',255);
update t set pad=dbms_random.string('l',255)
       *
ERROR at line 1:
ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS'   --->得到了错误提示，无法扩展undo，因为当前环境undo未启用自动扩展

--Author : Leshami
--Blog   : http://blog.csdn.net/leshami

--看看update语句到底产生了多少undo
sys@SYTST> @mystat2  
sys@