关于undo表空间大小设定的讨论

Oracle的undo数据就是事务中那些被更改，可是没有被提交的数据。

undo数据可以用来提供事务回滚，恢复事务或读一致性等特性。

undo表空间就是用来保留undo数据的。

undo表空间的大小取决于以下三方面的因素：
1.Oracle初始化参数UNDO_RETENTION
undo_retention值表示一个undo数据块最久能在undo表空间中停留的时间,单位是秒，缺省值为900。

undo表空间资源是循环利用的，在undo_retention时间内，undo数据块的内容是不会被释放掉，即不会被新的undo数据覆盖。

所以设置停留时间越久，undo 表空间就越大。

2.每秒产生的undo数据块
工作量很大，业务量大，事务很多时，每秒钟所产生undo数据块的个数越多，需要的undo 表空间越大。

select begin_time, end_time, undoblks from v$undostat;
BEGIN_TIME END_TEME UNDOBLKS
------------------ ------------------- ----------
28-SEP-O8 13:43:02 28-SEP-O8 13:44:18 19
28-SEP-O8 13:33:02 28-SEP-O8 13:43:18 1474
28-SEP-O8 13:23:02 28-SEP-O8 13:33:18 1347
28-SEP-O8 13:13:02 28-SEP-O8 13:23:18 16 28
此语句记录了undo数据块的历史利用情况，每隔10分钟刷一次。

此结果表示记录前40分钟别离用到undo数据块19个、1474个、1347个、1628个；
select addr,used_ublk from v$transaction;
ADDR USED_UBLK
--------- --------------
5932F4A0 863
此语句可以查看当前事务所需要数据块的个数，此结果表示，当前用户只有一个事务正在执行，此事务需要863个undo数据块。

事务越多，操作影响数据越多，需要的undo数据块也越多。

3. Oracle初始化参数DB_BLOCK_SIZE
如何计算所需undo表空间的大小：
1.计算业务顶峰期每秒产生undo数据块的个数：
SQL> select max(undoblks / ((end_time - begin_time)*24*3600)) from v$undostat;
2.取得undo数据块在undo表空间中可以保留的最长时间
show parameter undo_retention
3.取得数据块大小
show parameter db_block;
将以上三者的数据相乘就是所需undo表空间的大小数。

当undo表空间增加较快时，要从以下角度考虑进行控制：
1. 若是undo表空间是自动扩展的，将自动扩展关闭。

如果undo表空间自动扩展，即便有过时的undo段，它也不会重用，而是选择增大数据文件。

alter database datafile '<datafile path>' autoextend off;
2. 对数据文件进行裁剪；
alter database datafile '<datafile path>' resize <new size>;
3. 减少UNDO_RETENTION参数值。

设定UNDO_RETENTION参数值时，参考V$UNDOSTAT里MAXQUERYLEN（执行时间最长的事务的执行时间）。

select max(MAXQUERYLEN) from V$UNDOSTAT;
4. 创建新的更小的undo表空间：
SQL> create undo tablespace UNDO_TBS1 datafile 'undotbs1.dbf' size 100m; SQL> alter system set undo_tablespace=undo_tbs1;
SQL> drop tablespace undo_rbs0 including contents.
若是在删除以前undo表空间时出现ORA-30013错误，则需要等待所有事务提交以后，才能删除以前undo表空间
发现undo表空间不够的时候，增加undo表空间的大小，执行语句如下：
alter tablespace undotbs add datafile '/u01/oradata/undotbs2.dbf' size
700M autoextend on;
ORA-01555查询失败，其他事务产生的undo数据覆盖了undo表空间中查询需要的old 数据块。

-------------------------------------------------------------------------------
删除undo tablespace实验：
SQL> create undo tablespace undotbs2
datafile '/opt/oradata/oradata/orcl/undotbs02.dbf' SIZE 100m autoextend off;
Tablespace created.
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------ undo_management string AUTO
undo_retention integer 10800
undo_suppress_errors boolean FALSE
undo_tablespace string UNDOTBS1
SQL> alter system set undo_tablespace=UNDOTBS2 scope=both;
System altered.
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 10800
undo_suppress_errors boolean FALSE
undo_tablespace string UNDOTBS2
SQL> select segment_name ,tablespace_name ,segment_id from
dba_rollback_segs;
SEGMENT_NAME TABLESPACE_NAME SEGMENT_ID ------------------------------ ------------------------------ ----------
SYSTEM SYSTEM 0
_SYSSMU1$ UNDOTBS1 1
_SYSSMU2$ UNDOTBS1 2
_SYSSMU3$ UNDOTBS1 3
_SYSSMU4$ UNDOTBS1 4
_SYSSMU5$ UNDOTBS1 5
_SYSSMU6$ UNDOTBS1 6
_SYSSMU7$ UNDOTBS1 7
_SYSSMU8$ UNDOTBS1 8
_SYSSMU9$ UNDOTBS1 9
_SYSSMU10$ UNDOTBS1 10
SEGMENT_NAME TABLESPACE_NAME SEGMENT_ID ------------------------------ ------------------------------ ----------
_SYSSMU11$ UNDOTBS2 11
_SYSSMU12$ UNDOTBS2 12
_SYSSMU13$ UNDOTBS2 13
_SYSSMU14$ UNDOTBS2 14
_SYSSMU15$ UNDOTBS2 15
_SYSSMU16$ UNDOTBS2 16
_SYSSMU17$ UNDOTBS2 17
_SYSSMU18$ UNDOTBS2 18
_SYSSMU19$ UNDOTBS2 19
_SYSSMU20$ UNDOTBS2 20
21 rows selected.
SQL> select usn,status,xacts from v$rollstat;
USN STATUS XACTS
------------- --------------- -------------
0 ONLINE 0
10 PENDING OFFLINE 1
11 ONLINE 0
13 ONLINE 0
14 ONLINE 0
15 ONLINE 0
16 ONLINE 0
17 ONLINE 0
18 ONLINE 0
19 ONLINE 1
USN STATUS XACTS
------------- --------------- -------------
20 ONLINE 0
12 rows selected.
发现原UNDOTBS1的回滚段处于PENDING OFFLINE状态，并有一个事物存在，需要等到事务完成后，才能drop UNDOTBS1
SQL> select usn,status,xacts from v$rollstat;
USN STATUS XACTS
------------- --------------- -------------
10 PENDING OFFLINE 0
11 ONLINE 0
12 ONLINE 0
13 ONLINE 0
14 ONLINE 0
15 ONLINE 0
16 ONLINE 0
17 ONLINE 0
18 ONLINE 0
19 ONLINE 1
USN STATUS XACTS
------------- --------------- ------------- 20 ONLINE 0
12 rows selected.
SQL> select usn,status,xacts from v$rollstat;
USN STATUS XACTS
------------- --------------- -------------
11 ONLINE 0
12 ONLINE 0
13 ONLINE 0
14 ONLINE 0
15 ONLINE 0
16 ONLINE 0
17 ONLINE 0
18 ONLINE 0
19 ONLINE 1
20 ONLINE 0
11 rows selected.
SQL> drop tablespace undotbs1 including contents and datafiles; Tablespace dropped.。