SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
1175386
SQL> delete flash_tbl where id<10;
delete flash_tbl where id<10
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> conn scott/oracle
Connected.
SQL> delete flash_tbl where id<10;
9 rows deleted.
SQL> commit;
Commit complete.
SQL> exec dbms_flashback.enable_at_system_change_number(1175386);
PL/SQL procedure successfully completed.
SQL> select * from flash_tbl;
ID V
---------- -
10 I
11 J
12 K
13 L
14 M
15 N
16 O
17 P
18 Q
19 R
20 S
ID V
---------- -
1 /
2 A
3 B
4 C
5 D
6 E
7 F
8 G
9 H
20 rows selected.
如果要进行任何的DML/DDL，需要取消查询状态
SQL> exec dbms_flashback.disable;
PL/SQL procedure successfully completed.
但是……
SQL> select * from flash_tbl;
ID V
---------- -
10 I
11 J
12 K
13 L
14 M
15 N
16 O
17 P
18 Q
19 R
20 S
11 rows selected.
可见，结果却不能保存……
利用PLSQL游标来实现
declare
cursor c_tbl is select * from flash_tbl where id<10;
t_row c_tbl%rowtype;
begin
dbms_flashback.enable_at_system_change_number(1175386);
open c _tbl;
dbms_flashback.disable;
loop
fetch c_tbl into t_row;
exit when c_tbl%notfound;
insert into flash_tbl values(t_row.id,t_row.vl);
end loop;
close c_tbl;
commit;
end;
/
执行：
PL/SQL procedure successfully completed.
SQL> select * from flash_tbl;
ID V
---------- -
10 I
11 J
12 K
13 L
14 M
15 N
16 O
17 P
18 Q
19 R
20 S
ID V
---------- -
1 /
2 A
3 B
4 C
5 D
6 E
7 F
8 G
9 H
20 rows selected.
终于恢复了……