| Flashback与ORA-01466 | DBA之路：OoNiceDream |

Flashback与ORA-01466

作者：OoNiceDream【转载时请务必以超链接形式标明文章原始出处和作者信息】
链接：http://www.dbaroad.me/archives/2009/05/flashback_got_ora_01466.html

在Oracle 9iR2中，表属性修改与flashback时间应该相隔5分钟，否则就会报ORA-01466。但测试时发现，如果使用as of scn来flashback时，就算相隔5分钟，仍然报ORA-01466：

SQL> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
 
Session altered.
 
SQL> col scn for 999999999999999999
SQL> drop table test;
 
Table dropped.
 
SQL> create table test (d date);
 
Table created.
 
SQL> select dbms_flashback.get_system_change_number SCN, sysdate from dual;
 
                SCN SYSDATE
------------------- -------------------
     10751229983414 2009-05-14 18:53:08
 
SQL> insert into test values(sysdate);
 
1 row created.
 
SQL> commit;
 
Commit complete.
 
SQL> select dbms_flashback.get_system_change_number SCN, sysdate from dual;
 
                SCN SYSDATE
------------------- -------------------
     10751229983423 2009-05-14 18:53:33
 
SQL> exec dbms_lock.sleep(300);
 
PL/SQL procedure successfully completed.
 
SQL> select dbms_flashback.get_system_change_number SCN, sysdate from dual;
 
                SCN SYSDATE
------------------- -------------------
     10751231051348 2009-05-14 19:00:08
 
SQL> insert into test values(sysdate);
 
1 row created.
 
SQL> commit;
 
Commit complete.
 
SQL> select * from test as of scn 10751231051348;                          
select * from test as of scn 10751231051348
              *
ERROR at line 1:
ORA-01466: unable to read data - table definition has changed

这里查询5分钟后的SCN：10751231051348仍会报ORA-01466，后尝试等待更久的时间，仍不行
百思不得其解，最后开了个SR，得到回复是个BUG：5525497

PROBLEM:
——–
Flashback query using SELECT AS OF SCN fails with ORA-1466 when
SMON_SCN_TIME as more than 1 distinct value for the column SCN_WRP.

也就是说，当使用：“AS OF SCN”进行flashback时，当SMON_SCN_TIME表中存在多个不同的SCN_WRP值时，就有可能会报ORA-01466。

SQL> select scn_wrp, min(time_dp), max(time_dp)
  2  from sys.smon_scn_time
  3  group by scn_wrp;
 
   SCN_WRP MIN(TIME_DP)        MAX(TIME_DP)
---------- ------------------- -------------------
      2502 2009-05-08 04:40:21 2009-05-11 11:30:58
      2503 2009-05-11 11:36:10 2009-05-14 19:01:01

不过为什么会存在多个SCN_WRP值？SR里说是BUG，我也只找到这么点资料：

Whenever the SCN is incremented, the BASE component is incremented first
unil it reaches it maximum. Once the BASE reaches the maximum value
allowed, it is initialized to zero again after incrementing the WRAP by 1.

继续回到之前的ORA-01466问题，这里我把包含时间较老的一个SCN_WRP值delete掉了，再重复实验，不再报ORA-01466错误。当然，Oracle并不推荐这么做。

SQL> select scn_wrp, min(time_dp), max(time_dp)
  2  from sys.smon_scn_time
  3  group by scn_wrp;
 
   SCN_WRP MIN(TIME_DP)        MAX(TIME_DP)
---------- ------------------- -------------------
      2502 2009-05-08 04:40:21 2009-05-11 11:30:58
      2503 2009-05-11 11:36:10 2009-05-14 19:01:01
 
SQL> delete from sys.smon_scn_time
  2  where scn_wrp=2502;
 
704 rows deleted.
 
SQL> commit;
 
Commit complete.