试验中发现的FLASHBACK QUERY的陷阱

[Lian...@gmail.com]

　　今天在做FLASHBACK QUERY的试验，发觉一个奇怪的现象。

用如下语句获得最近一段时间内TEST表上的操作明细
SQL> SELECT a, b, versions_operation, versions_startscn,
versions_endscn,
2 TO_CHAR (versions_starttime, 'YYYYMMDDHH24MISS')
starttime,
3 TO_CHAR (versions_endtime, 'YYYYMMDDHH24MISS') endtime
4 FROM TEST
5 VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP
('20080208111456',
6
'YYYYMMDDHH24MISS'
7 ) AND
SYSTIMESTAMP
8 WHERE A = 0;
A B V VERSIONS_STARTSCN
VERSIONS_ENDSCN STARTTIME ENDTIME
---------------------------------------- - - -----------------
--------------- -------------- --------------
0 4 D
1131263 20080208120502
0 4 U 1130923
1131263 20080208115620 20080208120502
0 3 U 1130064
1130923 20080208112654 20080208115620
0 0 I 1129426
1130064 20080208111538 20080208112654

可以发现从时间戳20080208112654之后A=0的记录被更新为B=3，于是试图用如下语句获得20080208112655的记录情况，预期
是得到B=3的记录
SQL> SELECT A, B
2 FROM TEST AS OF TIMESTAMP
(TO_TIMESTAMP('20080208112655','YYYYMMDDHH24MISS'))
3 WHERE A = 0;
A B
---------------------------------------- -
0 0

意想不到的结果出现了，B还是0，换用SCN，问题依旧
SQL> SELECT A, B
2 FROM TEST AS OF SCN
(TIMESTAMP_TO_SCN(TO_TIMESTAMP('20080208112655','YYYYMMDDHH24MISS')))
3 WHERE A = 0;
A B
---------------------------------------- -
0 0

于是准备看看20080208112655的SCN有没有问题
SQL> SELECT
TIMESTAMP_TO_SCN(TO_TIMESTAMP('20080208112655','YYYYMMDDHH24MISS'))
SCN FROM DUAL;
SCN
----------
1130063

问题的原因出现了，20080208112655的SCN是1130063，而VERSIONS_STARTSCN 是1130064，所以
FLASHBACK QUERY得到了错误的结果。

通过如下语句查到第一个可用的时间戳，然后换用那个时间戳，终于的到了预期的结果。
SQL> SELECT SCN_TO_TIMESTAMP('1130063') FROM DUAL;
SCN_TO_TIMESTAMP('1130063')
---------------------------------------------------------------------------
08-2月 -08 11.26.54.000000000 上午
SQL> SELECT SCN_TO_TIMESTAMP('1130064') FROM DUAL;
SCN_TO_TIMESTAMP('1130064')
---------------------------------------------------------------------------
08-2月 -08 11.26.54.000000000 上午
SQL> SELECT SCN_TO_TIMESTAMP('1130065') FROM DUAL;
SCN_TO_TIMESTAMP('1130065')
---------------------------------------------------------------------------
08-2月 -08 11.26.54.000000000 上午
SQL> SELECT SCN_TO_TIMESTAMP('1130066') FROM DUAL;
SCN_TO_TIMESTAMP('1130066')
---------------------------------------------------------------------------
08-2月 -08 11.27.00.000000000 上午

SQL> SELECT A, B
2 FROM TEST AS OF TIMESTAMP
(TO_TIMESTAMP('20080208112700','YYYYMMDDHH24MISS'))
3 WHERE A = 0;
A B
---------------------------------------- -
0 3

所以，以后使用FLASHBACK QUERY时还是用SCN保险。