9i根据时间flashback query的误差

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

发表于 2007-04-09 11:04:21 目录：数据库|Oracle, Oracle 日常管理, 所有|All Blogs [RSS 2.0] 你可以发表评论, 或者从您的网站 trackback