FlashbackQuery闪回查询

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(obj

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_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.

终于恢复了……