Flashback Query是利用多版本读一致性从UNDO表空间读取操作前的记录。
一、基于TIMESTAMP
1.创建测试数据。
SQL> create table flash_tbl(id,vl) as
? 2? select rownum,oname from (select substr(object_name,1,1) oname from all_objects group
by substr(object_name,1) order by 1)
? 3? where rownum<=20;
2.删除数据
SQL> delete flash_tbl where id<10;
9 rows deleted.
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.
3.查询5分钟之前的数据
SQL> select * from FLASH_TBL as of timestamp sysdate-5/1440;
??????? ID V
---------- -
???????? 1 /
???????? 2 A
???????? 3 B
???????? 4 C
???????? 5 D
???????? 6 E
???????? 7 F
???????? 8 G
???????? 9 H
??????? 10 I
??????? 11 J
??????? ID V
---------- -
??????? 12 K
??????? 13 L
??????? 14 M
??????? 15 N
??????? 16 O
??????? 17 P
??????? 18 Q
??????? 19 R
??????? 20 S
20 rows selected.
SQL> select * from flash_tbl
? 2? ;
??????? 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.
4.恢复记录
SQL> insert into flash_tbl
? 2? select * from FLASH_TBL AS OF TIMESTAMP SYSDATE-5/1440
? 3? where id<10;
9 rows created.
SQL> commit;
Commit complete.
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.
二、基于SCN的查询
1.授权给SCOTT用户
SQL> show user
USER is "SYS"
SQL> grant execute on dbms_flashback to scott;
Grant succeeded.
SQL> grant select on v_$database to scott;
Grant succeeded.
2.获取当前SCN
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
???????????????? 1173761
3.删除数据
SQL> conn scott/oracle
Connected.
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
???????????????? 1174190
SQL> delete flash_tbl where id<10;
9 rows deleted.
SQL> commit;
Commit complete.
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.
4.基于SCN的查询
SQL> select * from flash_tbl as of scn 1174190;
??????? 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.
5.恢复数据
SQL> insert into flash_tbl select * from flash_tbl as of scn 1174190 where id <10;
9 rows created.
SQL> commit;
Commit complete.
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.
SCN 和 TINESTAMP的相互转换
SQL> select timestamp_to_scn(sysdate) from dual;
TIMESTAMP_TO_SCN(SYSDATE)
-------------------------
????????????????? 1174474
SQL> select to_char(scn_to_timestamp(1174474),'yyyy-mm-dd') from dual;
TO_CHAR(SC
----------
2014-03-31
SQL> conn / as sysdba
Connected.
查询最小的SCN
SQL> select scn_wrp*4294967296+scn_bas from sys.smon_scn_time
? 2? where time_mp=(select min(time_mp) from sys.smon_scn_time);
SCN_WRP*4294967296+SCN_BAS
--------------------------
???????????????????????? 4
SQL> select scn_to_timestamp(4) from dual;
SCN_TO_TIMESTAMP(4)
---------------------------------------------------------------------------
30-JUN-05 07.09.55.000000000 PM
SQL> select sysdate from dual;
SYSDATE
---------
31-MAR-14
SQL> select scn_to_timestamp(3) from dual;
select scn_to_timestamp(3) from dual
?????? *
ERROR at line 1:
ORA-08181: specified number is not a valid system change number
ORA-06512: at "SYS.SCN_TO_TIMESTAMP",line 1
SQL> select scn_to_timestamp(5) from dual;
SCN_TO_TIMESTAMP(5)
---------------------------------------------------------------------------
30-JUN-05 07.09.59.000000000 PM
SQL> select scn_to_timestamp(100) from dual;
SCN_TO_TIMESTAMP(100)
---------------------------------------------------------------------------
30-JUN-05 07.10.14.000000000 PM
利用DBMS_FLASHBACK包实现Flashback
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.
终于恢复了……
