關於 oracle 9i 閃回的一個錯誤補
昨天說閃回需要等五分鐘才能開始操作，我查了一下資料和博友的博文，發下問題是這樣的：oracle資料庫每五分鐘記錄一次scn，記錄在smon_scn_time裡邊，可以透過查詢來檢視。
經過測試後，發現，透過execute dbms_flashback.disable 關閉閃回模式，可以等五分鐘後，就可以透過閃回恢復。如果沒有關閉的話，就需要等五分鐘，生成scn，才能進行有閃回，不然的話，會報出ora-01446的錯誤，這個問題終於整明白了。
謝謝幾位博友，參照了他們的博文，才弄明白！
下邊是測試的語句：
Microsoft Windows XP [版本 5.1.2600]
(C) 版權所有 1985-2001 Microsoft Corp.
(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
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> conn test/test;
已連線。
SQL> create table test(id int);
已連線。
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
------------------------
368297
SQL> delete from test where id=1;
已刪除 1 行。
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
368667
------------------------
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;
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
------------------------
369036
已用時間: 00: 00: 00.00
---------------中間閃回失敗-----------------------------------------------
-----------------方法和下邊一樣，報錯誤ora-01446-----------------
----------------原因：沒有經過五分鐘，沒有生成刪除資料的scn------------
----------------可以按照下邊的補助測試----------------------------------
08:30:06 SQL> conn as sysdba;
已連線。
08:30:06 SQL> conn 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
---------- -------------------
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
---------- -------------------
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
---------- -------------------
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
---------- -------------------
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
---------- -------------------
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
---------- -------------------
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
---------- -------------------
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
---------- -------------------
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
---------- -------------------
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
---------- -------------------
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
---------- -------------------
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;
08:47:14 SQL> conn test/test;
已連線。
08:47:44 SQL> select * from test as of scn 369985;
ID
----------
2
4
5
6
----------
2
4
5
6
已用時間: 00: 00: 00.00
08:48:06 SQL> select * from test as of scn 372337;
08:48:06 SQL> select * from test as of scn 372337;
ID
----------
5
6
----------
5
6
已用時間: 00: 00: 00.00
08:49:20 SQL> select * from test as of scn 371143;
08:49:20 SQL> select * from test as of scn 371143;
ID
----------
5
6
----------
5
6
已用時間: 00: 00: 00.00
08:49:34 SQL> select * from test as of scn 368903;
08:49:34 SQL> select * from test as of scn 368903;
ID
----------
2
4
5
6
----------
2
4
5
6
已用時間: 00: 00: 00.00
08:50:10 SQL> select * from test;
08:50:10 SQL> select * from test;
ID
----------
5
6
----------
5
6
已用時間: 00: 00: 00.00
08:50:49 SQL> insert into test select * from test as of scn 368903 where id<=4;
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;
08:51:26 SQL> commit;
提交完成。
已用時間: 00: 00: 00.00
08:51:31 SQL> select * from test;
08:51:31 SQL> select * from test;
ID
----------
5
6
2
4
----------
5
6
2
4
已用時間: 00: 00: 00.00
08:51:49 SQL> select dbms_flashback.get_system_change_number from dual;
08:51:49 SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
373959
------------------------
373959
已用時間: 00: 00: 00.00
08:52:26 SQL> execute dbms_flashback.disable;
08:52:26 SQL> execute dbms_flashback.disable;
PL/SQL 過程已成功完成。
已用時間: 00: 00: 00.00
08:52:54 SQL> delete from test;
08:52:54 SQL> delete from test;
已刪除4行。
已用時間: 00: 00: 00.00
08:53:04 SQL> commit;
08:53:04 SQL> commit;
提交完成。
已用時間: 00: 00: 00.00
08:53:08 SQL> select dbms_flashback.get_system_change_number from dual;
08:53:08 SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
374085
------------------------
374085
已用時間: 00: 00: 00.00
08:53:12 SQL> select * from test;
08:53:12 SQL> select * from test;
未選定行
已用時間: 00: 00: 00.00
08:53:22 SQL> select * from test as of scn 373959;
08:53:22 SQL> select * from test as of scn 373959;
ID
----------
5
6
2
4
----------
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: 表名無效
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;
08:55:02 SQL> commit;
提交完成。
已用時間: 00: 00: 00.00
08:55:18 SQL> select * from test;
08:55:18 SQL> select * from test;
ID
----------
5
6
2
4
----------
5
6
2
4
已用時間: 00: 00: 00.00
08:55:28 SQL> select * from test order by id;
08:55:28 SQL> select * from test order by id;
ID
----------
2
4
5
6
----------
2
4
5
6
已用時間: 00: 00: 00.00
08:55:43 SQL>
08:55:43 SQL>
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22275400/viewspace-626611/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 關於oracle 9i的閃回的一個錯誤Oracle
- oracle 9i 閃回Oracle
- Oracle 9i閃回測試。Oracle
- 閃回 錯誤的DML 操作
- 閃回（關於閃回查詢）
- Oracle閃回誤刪的表Oracle
- 閃回（關於閃回資料庫）資料庫
- 關於oracle閃回資料歸檔的總結Oracle
- oracle關於*_tab_columns檢視的描述有一個錯誤Oracle
- 一個關於資料庫閃回區問題的處理資料庫
- Oracle EBS 打中文補丁錯誤 的一個解決方法Oracle
- 【FLASHBACK】關於閃回資料庫的一點說明資料庫
- 關於 curl 工作中一個小錯誤
- 利用undo的閃回特性恢復錯誤操作的表
- oracle閃回Oracle
- oracle 閃回Oracle
- oracle 閃回基於時間的恢復Oracle
- 使用oracle 閃回查詢找回誤更新的資料Oracle
- 關於vuex的錯誤Vue
- 關於weblogic pool 一錯誤除錯Web除錯
- Oracle閃回刪除恢復誤刪資料Oracle
- oracle 閃回 flashbackOracle
- oracle閃回特性Oracle
- oracle關於ORA-12988錯誤Oracle
- oracle的閃回查詢Oracle
- oracle的回閃查詢Oracle
- 關於ORACLE_OCM.MGMT_DB_LL_METRICS的錯誤Oracle
- 關於Oracle8i回退段引起的錯誤Oracle
- 關於Oracle 9i RAC enqueue等待的一點測試OracleENQ
- Oracle閃回技術 為Oracle閃回配置資料庫Oracle資料庫
- Oracle Flashback 閃回查詢功能操作範例(9i and 10g)Oracle
- 關於”kccrsz“錯誤處理一則
- 關於ORACLE大型事務回滾的幾個點Oracle
- 閃回技術一：閃回查詢
- Oracle閃回技術之閃回資料庫Oracle資料庫
- Oracle 11G 閃回技術 使用Oracle閃回查詢Oracle
- oralce恢復誤刪除的表中的資料（閃回、閃回查詢）
- oracle閃回查詢Oracle