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,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_FL