9i的时候，根据时间来做flashback query，是很容易有比较大的误差的，不过幸好的是，10g改进了这一点，其实，主要的原因是因为，9i 的scn与时间的同步问题，需要5分钟以后才能同步，也就是说，如果新插入的数据，还不到5分钟，马上就根据时间来flashback query，是查不到数据的。我们下面来看一则误差是怎么产生的：
11:22:08 Piner@9iR2>select * from test;
A
----------
1
2
3
11:22:11 Piner@9iR2>select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),
to_char(DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER) from dual;
TO_CHAR(SYSDATE,'YY TO_CHAR(DBMS_FLASHBACK.GET_SYSTEM_CHANGE
------------------- ----------------------------------------
2007-04-09 11:22:25 54561295523
11:22:28 Piner@9iR2>delete from test;
3 rows deleted.
11:22:35 Piner@9iR2>commit;
Commit complete.
11:22:39 Piner@9iR2>SELECT * FROM test AS OF TIMESTAMP
TO_TIMESTAMP('2007-04-09 11:22:25', 'YYYY-MM-DD HH:MI:SS');
A
----------
1
2
3
11:23:41 Piner@9iR2>SELECT * FROM test AS OF SCN 54561295523;
A
----------
1
2
3
11:24:08 Piner@9iR2>insert into test values(1);
1 row created.
11:24:48 Piner@9iR2>insert into test values(3);
1 row created.
11:24:55 Piner@9iR2>insert into test values(5);
1 row created.
11:25:01 Piner@9iR2>commit;
Commit complete.
11:25:04 Piner@9iR2>select * from test;
A
----------
1
3
5
11:25:08 Piner@9iR2>select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),
to_char(DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER) from dual;
TO_CHAR(SYSDATE,'YY TO_CHAR(DBMS_FLASHBACK.GET_SYSTEM_CHANGE
------------------- ----------------------------------------
2007-04-09 11:25:17 54561295583
11:25:17 Piner@9iR2>delete from test;
3 rows deleted.
11:25:21 Piner@9iR2>commit;
Commit complete.
11:25:23 Piner@9iR2>SELECT * FROM test AS OF TIMESTAMP
TO_TIMESTAMP('2007-04-09 11:25:17', 'YYYY-MM-DD HH:MI:SS');
A
----------
1
2
3
11:25:36 Piner@9iR2>SELECT * FROM test AS OF SCN 54561295583;
A
----------
1
3
5
11:34:02 Piner@9iR2>SELECT * FROM test AS OF TIMESTAMP
TO_TIMESTAMP('2007-04-09 11:30:17', 'YYYY-MM-DD HH:MI:SS');
A
----------
1
2
3
上一篇： « 再也不要笑话别人的身体差了
下一篇： 太气愤了，HDS的高端存储USP有点太垃圾了。 »
- 发表评论
