LaoHuang

现象：
利用闪回查询来获得某个表的5分钟这内的变化，UNDO的保留时间参数设置的是3小时，UNDO表空间足够。用一个JOB每隔十分钟将该表的5分钟内的变化记录总数插入到另一个记录表中。具体的SQL如下：
insert into M_A_LOG
(
select sysdate-5/24/60 as fromtime, sysdate as totime, count(1) as frames from
(
select DATANO from A
minus
select DATANO from A as of timestamp sysdate - 5/24/60
)
);
运行约半个月没有问题，今天ORACLE日志中出现错误，ORA－1555，说：ORA-01555: 快照过旧: 回退段号 1 在名称为 "_SYSSMU1$" 过小
该语句执行时间绝不会超过3小时，不知何因。

fly115

看看你undospace有多大
UndoSpace = [UR * (UPS * DBS)] + (DBS * 24)


(UR) UNDO_RETENTION in seconds
(UPS) Number of undo data blocks generated per second
(DBS) Overhead varies based on extent and file size (db_block_size)

SELECT (UR * (UPS * DBS)) + (DBS * 24) AS "Bytes"
    FROM (SELECT value AS UR
    FROM v$parameter
   WHERE name = 'undo_retention'),
   (SELECT (SUM(undoblks)/SUM
            (((end_time-begin_time)*86400))) AS UPS
   FROM v$undostat),
    (SELECT value AS DBS
   FROM v$parameter
   WHERE name = 'db_block_size');