ORA-01555:snapshot too old, rollback segment number xx with name "xxxx" too small.

现象

用SQL从对方Oracle数据库导出很大量数据的时候，执行时间特别长，报如题错误：
大概意思就是快照太旧，回滚段太小。

分析

原因大概如下：

【1】SQL执行太久

这个和具体业务有关系。
除了SQLSQL本身可以设法优化以外，主要可能后面两个问题导致的。

【2】回滚段太小

检查回滚表空间大小，看看回滚表空间UNDOTBS1（得看看表空间名字对不对）总大小够不够，使用率情况是不是正常（占满了？），当然此例中是本机的测试数据库，所以并没有问题。如果结合实际发现太小则可以扩大一些：

SQL> SELECT a.tablespace_name,
  2  to_char(a.bytes/ (1024 * 1024),'FM999990')||'MB' as "total",
  3  to_char(b.bytes/ (1024 * 1024),'FM999990')||'MB (' || to_char( (b.bytes * 100) / a.bytes,'FM990.0' )||'%)' as "used",
  4  to_char(c.bytes/ (1024 * 1024),'FM999990')||'MB (' || to_char( (c.bytes * 100) / a.bytes,'FM990.0' )||'%)' as "free"
  5  FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c
  6  WHERE a.tablespace_name = b.tablespace_name
  7  AND a.tablespace_name = c.tablespace_name;

TABLESPACE_NAME                total     used                free
------------------------------ --------- ------------------- -------------------
SYSAUX                         540MB     497MB (92.0%)       42MB (7.8%)
UNDOTBS1                       725MB     55MB (7.6%)         669MB (92.2%)
SDAMP_DATA                     10240MB   2481MB (24.2%)      7975MB (77.9%)
USERS                          5MB       1MB (25.0%)         3MB (55.0%)
SYSTEM                         710MB     704MB (99.2%)       5MB (0.7%)

SQL>

【3】快照太旧

查看undo_retention参数，Oracle官方文档是这样写的：

UNDO_RETENTION specifies (in seconds) the low threshold value of undo retention. For AUTOEXTEND undo tablespaces, the system retains undo for at least the time specified in this parameter, and automatically tunes the undo retention period to satisfy the undo requirements of the queries. For fixed- size undo tablespaces, the system automatically tunes for the maximum possible undo retention period, based on undo tablespace size and usage history, and ignores UNDO_RETENTION unless retention guarantee is enabled.
.
The setting of this parameter should account for any flashback requirements of the system. Automatic tuning of undo retention is not supported for LOBs. The RETENTION value for LOB columns is set to the value of the UNDO_RETENTION parameter.
.
The UNDO_RETENTION parameter can only be honored if the current undo tablespace has enough space. If an active transaction requires undo space and the undo tablespace does not have available space, then the system starts reusing unexpired undo space. This action can potentially cause some queries to fail with a “snapshot too old” message.
.
The amount of time for which undo is retained for the Oracle Database for the current undo tablespace can be obtained by querying the TUNED_UNDORETENTION column of the V$UNDOSTAT dynamic performance view.

下面这段不知道是谁翻译的：undo_retention：是undo数据保存的时间，是一个“目标期望值”，单位为秒，默认为900s（范围为：0 到 2的32次方 - 1 ）。用户设置出这个值之后，Oracle内部会尽量保证将Undo数据保留超过undo_retention设置的时间。但是，如果Undo使用紧张、没有额外的方法，那么这个时间段也是不能保证的。这个时间如果设置得比较小，数据很快被覆盖了，那么执行较长时间的查询语句就会报ORA-01555的错误。

SQL> show parameter undo_

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

SQL>

如果时间设置得比较短，可以修改：
如果操作频繁资源的确紧张就没办法了。。。

alter  system set undo_retention=7200;

解决

并没有解决，因为不是我们自己的数据库，没法调整任何参数。。。呵呵哒。。。