2011年8月3日
检查告警日志信息发现如下错误：
ORA-12012: error on auto execute of job 1925127
ORA-01555: snapshot too old: rollback segment number 19 with name "_SYSSMU19_1272485971$" too small
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 2483
ORA-06512: at "SYS.DBMS_STATS", line 24063

Oracle Support解释发生ORA-01555的原因如下：
1. Few number of rollback segments with potentially small sizes in a
very active database (e.g., multiple users doing on-line transaction
processing -- OLTP).

2. Corrupted rollback segment prevents a consistent read requested by a
query from being able to read the block in the rollback segment.

3. Fetch across commits within an open cursor (cursors retain the "snapshot"
of the query at cursor open time).

4. Fetch across commits with delayed block cleanout (the data block is not
updated with the last committed image until the next reader accesses the
data block, which requires verification from the transaction table of
the rollback segment).

解决办法：
回滚段“_SYSSMU19_1272485971$”太小引起ORA-01555错误。建议扩大UNDO表空间或者调整undo_retention时间和优化SQL语句。

查看UNDO表空间的使用情况：
SQL> SELECT A.TABLESPACE_NAME TBS_NAME,A.TOTAL_BYTES "TBS_SIZE(M)",A.TOTAL_BYTES-B.FREE_BYTES "TBS_USED(M)",ROUND((A.TOTAL_BYTES-B.FREE_BYTES)/A.TOTAL_BYTES*100,2) "TBS_USE",B.FREE_BYTES "TBS_FREE(M)"
FROM
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/1024/1024,2) TOTAL_BYTES FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME)A,
(SELECT TABLESPACE_NAME,ROUND(SUM(BYTES)/1024/1024,2) FREE_BYTES FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME)B
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME='UNDOTBS1';

TBS_NAME TBS_SIZE(M) TBS_USED(M) TBS_USE TBS_FREE(M)
-------------------- ----------- ----------- ---------- -----------
UNDOTBS1 126948 59557.69 46.92 67390.31

查看视图建议值：
select begin_time,end_time,undoblks,tuned_undoretention,maxquerylen,maxqueryid from v$undostat order by begin_time;

查看系统UNDO相关的参数
SQL> show parameter undo

NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
undo_management string AUTO
undo_retention integer 300
undo_tablespace string UNDOTBS1

修改undo_retention的参数值
SQL> ALTER SYSTEM SET undo_retention=900 SCOPE=BOTH

查看UNDO表空间的RETENTION的值
SQL> select tablespace_name,retention from dba_tablespaces where tablespace_name='UNDOTBS1';

TABLESPACE_NAME RETENTION
------------------------------ -----------
UNDOTBS1 NOGUARANTEE
如果UNDO表空间的RETENTION设置为GUARANTEE意味着UNDO表空间必须满足UNDO_RETENTION的时间限制，即UNDO信息的保留时间必须满足UNDO_RETENTION设定的值。
1.如果设置为NOGUARANTEE。ORACLE并不能保证能够将UNDO信息的保留时间满足UNDO_RETENTION设定的值，如果undo表空间不足，那么ORACLE将忽略UNDO_RETENTION的设置，直接覆盖掉以前的UNDO,这个时候有可能会产生ORA-01555错误。如果UNDO表空间空间足够，那么undo将会保存很长一段时间，直到undo表空间达到maxsize，这个时候才会覆盖undo信息，而且ORACLE会从保存时间最长的UNDO信息开始覆盖。ORACLE推荐我们将UNDO表空间中的datafile设定MAXSIZE，不要让它一直自动扩展，如果ORACLE获得了自动扩展的能力，那么旧的UNDO不会被覆盖，到后来UNDO表空间会越来越大，直到将磁盘空间耗尽。

2.如果设置为GUARANTEE。ORACLE将会保证UNDO信息能够保存到UNDO_RETENTION设定的值之后才允许被覆盖，如果这个时候同时执行了很多事物，将UNDO表空间耗完了，那么那个事物会失败，会报ORA-30036错误，所以使用guarantee一定要慎用，如果非要使用guarantee，那么尽量将undo表空间设大一点。Oracle10g开始，如果设置UNDO_RETENTION为0，那么Oracle启用自动调整以满足最长运行查询的需要。当然如果空间不足，那么Oracle满足最大允许的长时间查询。而不再需要用户手工调整。

3.启用或者禁用UNDO表空间RETENTION
启用：
SQL> ALTER TABLESPACE UNDOTBS1 RETENTION GUARANTEE;
禁用：
SQL> ALTER TABLESPACE UNDOTBS1 RETENTION NOGUARANTEE;