检查及设置合理的undo表空间
UNDO是用于实现并发控制以及构建一致性读，也就是在数据变更之前产生前镜像，以保证用户能够回滚或撤销对
数据库所作的修改。是
Oracle数据库完整性的重要组成部分。因此合理的设计及配置undo以及使用undo都将对数据库有较大的影响。通常情况下，对于大规模数据的删除，更新操作，我们建议使用分批删除分次提交以减少对undo的占用和冲击。那么对于undo的大小到底应该设置多大？是启用自动扩展还是关闭自动扩展？这个问题仁者见仁，智者见智，见下文。
1、当前数据库环境及undo配置信息
[sql]
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超出并获得建议值
[sql]
--先查看当前已产生的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@SYTST> set echo off
NAME V DIFF
---------------------------------------------------------------- ---------- ----------------
undo change vector size 18817316 18,453,748
--由下可知，上面的update语句已经产生了17M以上的undo,而当前的undo的大小为20M，显然不够，所以我们收到了ORA-30036
sys@SYTST> select 18453748/1024/1024 from dual;
18453748/1024/1024
------------------
17.5988655
--再次获得undo建议值
sys@SYTST> @chk_advs_undo