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
2.删除数据
SQL> delete flash_tbl where id
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
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
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
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
delete flash_tbl where id
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> conn scott/oracle
Connected.
SQL> delete flash_tbl where id
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
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.
终于恢复了……