昨天说闪回需要等五分钟才能开始操作，我查了一下资料和博友的博文，发下问题是这样的：oracle数据库每五分钟记录一次scn，记录在smon_scn_time里边，可以通过查询来查看。

经过测试后，发现，通过execute dbms_flashback.disable 关闭闪回模式，可以等五分钟后，就可以通过闪回恢复。如果没有关闭的话，就需要等五分钟，生成scn，才能进行有闪回，不然的话，会报出ora-01446的错误，这个问题终于整明白了。

谢谢几位博友，参照了他们的博文，才弄明白！

下边是测试的语句：

Microsoft Windows XP [版本 5.1.2600]
(C) 版权所有 1985-2001 Microsoft Corp.

C:\Documents and Settings\Administrator>sqlplus/nolog

SQL*Plus: Release 9.2.0.1.0 - Production on 星期二 2月 2 08:24:42 2010

SQL> conn test/test;
已连接。
SQL> create table test(id int);

表已创建。

SQL> insert into test values(1);

已创建 1 行。

SQL> insert into test values(2);

已创建 1 行。

SQL> insert into test values(3);

已创建 1 行。

SQL> insert into test values(4);

已创建 1 行。

SQL> insert into test values(5);

已创建 1 行。

SQL> insert into test values(6);

已创建 1 行。

SQL> commit;

提交完成。

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
368297

SQL> delete from test where id=1;

已删除 1 行。

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
368667

SQL> delete from test where id=3;

已删除 1 行。

SQL> commit;

提交完成。

SQL> set timing on;
SQL> sel time on;
SP2-0734: 未知的命令开头 "sel time o..." - 忽略了剩余的行。
SQL> set time on;
08:29:57 SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
369036

已用时间: 00: 00: 00.00

---------------中间闪回失败-----------------------------------------------

-----------------方法和下边一样，报错误ora-01446-----------------

----------------原因：没有经过五分钟，没有生成删除数据的scn------------

----------------可以按照下边的补助测试----------------------------------
08:30:06 SQL> conn sys/sys@orastudy as sysdba;
已连接。

SQL> select scn_bas,to_char(time_dp,'yyyy-mm-dd hh24:mi:ss') from smon_scn_time;
   SCN_BAS TO_CHAR(TIME_DP,'YY
---------- -------------------
    204221 2010-01-28 23:03:02
    205250 2010-01-28 23:08:10
    207086 2010-01-28 23:17:14
    208112 2010-01-28 23:22:21
    209097 2010-01-28 23:27:29
    210161 2010-01-28 23:32:36
    211242 2010-01-28 23:37:43
    212377 2010-01-28 23:42:51
    213507 2010-01-28 23:49:32
    214675 2010-01-28 23:54:40
    215942 2010-01-29 00:00:38

   SCN_BAS TO_CHAR(TIME_DP,'YY
---------- -------------------
    217006 2010-01-29 00:05:46
    218133 2010-01-29 00:10:53
    219024 2010-01-29 00:16:01
    239231 2010-01-29 07:59:13
    240202 2010-01-29 08:04:23
    240410 2010-02-01 10:19:04
    241496 2010-02-01 10:24:13
    242517 2010-02-01 10:29:20
    243564 2010-02-01 10:34:27
    244582 2010-02-01 10:39:35
    245660 2010-02-01 10:44:42

   SCN_BAS TO_CHAR(TIME_DP,'YY
---------- -------------------
    246689 2010-02-01 10:49:49
    247709 2010-02-01 10:54:57
    248753 2010-02-01 11:00:04
    249792 2010-02-01 11:05:11
    250798 2010-02-01 11:10:18
    251791 2010-02-01 11:15:25
    252979 2010-02-01 11:20:33
    254131 2010-02-01 11:25:41
    255205 2010-02-01 11:30:48
    256331 2010-02-01 11:35:55
    257473 2010-02-01 11:41:02

   SCN_BAS TO_CHAR(TIME_DP,'YY
---------- -------------------
    258742 2010-02-01 11:47:06
    259892 2010-02-01 11:52:13
    261075 2010-02-01 11:57:20
    262810 2010-02-01 12:05:37
    263886 2010-02-01 12:10:44
    264955 2010-02-01 12:15:51
    265873 2010-02-01 12:20:59
    266941 2010-02-01 12:26:08
    268087 2010-02-01 12:31:15
    269166 2010-02-01 12:36:22
    270347 2010-02-01 12:41:29

   SCN_BAS TO_CHAR(TIME_DP,'YY
---------- -------------------
    271492 2010-02-01 12:46:37
    272490 2010-02-01 12:51:44
    273627 2010-02-01 12:56:51
    274742 2010-02-01 13:01:58
    275803 2010-02-01 13:07:06
    276947 2010-02-01 13:12:13
    278131 2010-02-01 13:17:20
    279258 2010-02-01 13:22:27
    280340 2010-02-01 13:27:35
    281512 2010-02-01 13:32:42
    282683 2010-02-01 13:37:50

   SCN_BAS TO_CHAR(TIME_DP,'YY
---------- -------------------
    283765 2010-02-01 13:42:57
    284946 2010-02-01 13:48:04
    286095 2010-02-01 13:53:11
    287178 2010-02-01 13:58:18
    288363 2010-02-01 14:03:26
    289454 2010-02-01 14:08:33
    290477 2010-02-01 14:13:44
    292194 2010-02-01 14:21:02
    293250 2010-02-01 14:26:09
    294395 2010-02-01 14:31:17
    295973 2010-02-01 14:38:27

   SCN_BAS TO_CHAR(TIME_DP,'YY
---------- -------------------
    297113 2010-02-01 14:43:34
    298262 2010-02-01 14:48:49
    299461 2010-02-01 14:54:24
    300558 2010-02-01 14:59:32
    301677 2010-02-01 15:04:38
    303821 2010-02-01 15:14:18
    305597 2010-02-01 15:22:13
    306627 2010-02-01 15:27:20
    307796 2010-02-01 15:33:42
    309272 2010-02-01 15:40:43
    310242 2010-02-01 15:45:50

   SCN_BAS TO_CHAR(TIME_DP,'YY
---------- -------------------
    311251 2010-02-01 15:50:58
    312256 2010-02-01 15:56:07
    313279 2010-02-01 16:01:15
    314323 2010-02-01 16:06:22
    315406 2010-02-01 16:11:29
    316497 2010-02-01 16:16:36
    317485 2010-02-01 16:21:44
    318636 2010-02-01 16:26:51
    319756 2010-02-01 16:31:58
    320752 2010-02-01 16:37:07
    321870 2010-02-01 16:42:15

   SCN_BAS TO_CHAR(TIME_DP,'YY
---------- -------------------
    322933 2010-02-01 16:47:22
    324010 2010-02-01 16:52:30
    325164 2010-02-01 16:57:37
    326311 2010-02-01 17:02:44
    328077 2010-02-01 17:10:31
    329122 2010-02-01 17:15:38
    330231 2010-02-01 17:20:45
    331211 2010-02-01 17:25:53
    332122 2010-02-01 17:31:00
    353085 2010-02-01 19:11:53
    354151 2010-02-01 19:17:01

   SCN_BAS TO_CHAR(TIME_DP,'YY
---------- -------------------
    355434 2010-02-01 19:22:48
    356438 2010-02-01 19:27:55
    357514 2010-02-01 19:33:04
    358583 2010-02-01 19:38:11
    359633 2010-02-01 19:43:18
    360665 2010-02-01 19:48:25
    361811 2010-02-01 19:53:33
    362966 2010-02-01 19:58:40
    364041 2010-02-01 20:03:47
    365044 2010-02-02 08:11:55
    366224 2010-02-02 08:17:06

   SCN_BAS TO_CHAR(TIME_DP,'YY
---------- -------------------
    367294 2010-02-02 08:22:13
    368903 2010-02-02 08:29:31
    369985 2010-02-02 08:34:38
    371143 2010-02-02 08:39:45
    372337 2010-02-02 08:44:53

已选择159行。

已用时间: 00: 00: 00.03
08:47:14 SQL> conn test/test;
已连接。
08:47:44 SQL> select * from test as of scn 369985;

        ID
----------
         2
         4
         5
         6

已用时间: 00: 00: 00.00
08:48:06 SQL> select * from test as of scn 372337;

        ID
----------
         5
         6

已用时间: 00: 00: 00.00
08:49:20 SQL> select * from test as of scn 371143;

        ID
----------
         5
         6

已用时间: 00: 00: 00.00
08:49:34 SQL> select * from test as of scn 368903;

        ID
----------
         2
         4
         5
         6

已用时间: 00: 00: 00.00
08:50:10 SQL> select * from test;

        ID
----------
         5
         6

已用时间: 00: 00: 00.00
08:50:49 SQL> insert into test select * from test as of scn 368903 where id<=4;

已创建2行。

已用时间: 00: 00: 00.00
08:51:26 SQL> commit;

提交完成。

已用时间: 00: 00: 00.00
08:51:31 SQL> select * from test;

        ID
----------
         5
         6
         2
         4

已用时间: 00: 00: 00.00
08:51:49 SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
373959

已用时间: 00: 00: 00.00
08:52:26 SQL> execute dbms_flashback.disable;

PL/SQL 过程已成功完成。

已用时间: 00: 00: 00.00
08:52:54 SQL> delete from test;

已删除4行。

已用时间: 00: 00: 00.00
08:53:04 SQL> commit;

提交完成。

已用时间: 00: 00: 00.00
08:53:08 SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER
------------------------
374085

已用时间: 00: 00: 00.00
08:53:12 SQL> select * from test;

未选定行

已用时间: 00: 00: 00.00
08:53:22 SQL> select * from test as of scn 373959;

        ID
----------
         5
         6
         2
         4

已用时间: 00: 00: 00.00
08:53:40 SQL> insert into select * from test as of scn 373959;
insert into select * from test as of scn 373959
*
ERROR 位于第 1 行:
ORA-00903: 表名无效

已用时间: 00: 00: 00.00
08:54:45 SQL> insert into test select * from test as of scn 373959;

已创建4行。

已用时间: 00: 00: 00.00
08:55:02 SQL> commit;

提交完成。

已用时间: 00: 00: 00.00
08:55:18 SQL> select * from test;

        ID
----------
         5
         6
         2
         4

已用时间: 00: 00: 00.00
08:55:28 SQL> select * from test order by id;

        ID
----------
         2
         4
         5
         6

已用时间: 00: 00: 00.00
08:55:43 SQL>

ITPub博客

关于 oracle 9i 闪回的一个错误补

最新文章