flashback之flashback query

来源：互联网发布：淘宝信用卡哪个好编辑：程序博客网时间：2024/05/01 13:46

《三思笔记》--读书笔记

语法格式是在标准查询语句的from表名后面加上as of timestamp（基于时间）或as of scn（基于scn）

测试表：

SQL> conn cindy/cindyConnected.SQL> create table flash_tbl(id,vl) as   2  select rownum,oname from (select substr(object_name,1,1) oname from all_objects  3  group by substr(object_name,1,1) order by 1)  4  where rownum <=20;Table created.

flashback query 这一特性，最常被应用的就是修复误操作的数据，这并不是说flashback query 能够恢复数据，flashback query 本身不会恢复任何操作或修改，也不能告诉我们做过什么操作或修改，实际上flashback query 特性实际应用时，是基于标准select 的扩展，借助该特性能够让用户查询到指定的时间点的表中的记录，相当于拥有了看到过去的能力

（1），基于时间的查询（as of timestamp）

删除几条数据

SQL> delete flash_tbl where id<10;9 rows deleted.SQL> commit;Commit complete.SQL> select * from flash_tbl;        ID VL---------- --        10 I        11 J        12 K        13 L        14 M        15 N        16 O        17 P        18 Q        19 R        20 S11 rows selected.

删除的数据已经提交，若删除动作在5分钟之内，那么可以利用flashback query来恢复记录

首先找到数据

SQL> select * from flash_tbl as of timestamp sysdate-5/1440;        ID VL---------- --         1 /         2 A         3 B         4 C         5 D         6 E         7 F         8 G         9 H        10 I        11 J        ID VL---------- --        12 K        13 L        14 M        15 N        16 O        17 P        18 Q        19 R        20 S20 rows selected.

下面快速将记录恢复

SQL> insert into flash_tbl  2  select * from flash_tbl as of timestamp sysdate-5/1440      3  where id <10;9 rows created.SQL> select * from flash_tbl;        ID VL---------- --        10 I        11 J        12 K        13 L        14 M        15 N        16 O        17 P        18 Q        19 R        20 S        ID VL---------- --         1 /         2 A         3 B         4 C         5 D         6 E         7 F         8 G         9 H20 rows selected.

数据被成功恢复。

as of timestamp 使用方便，但是某些情况下，需要对多个相互有主外键约束的表进行恢复时，如果使用as of timestamp的方式，可能会由于时间点不统一的缘故造成数据选择或插入失败，所以as of scn较好

（2）基于scn的查询（as of scn）

授予用户使用dbms_flashback包的权限

SQL> conn / as sysdbaConnected.SQL> grant execute on dbms_flashback to cindy;Grant succeeded.SQL> grant select on v_$database to cindy;Grant succeeded.

首先，获取scn

SQL> select current_scn from v$database;CURRENT_SCN-----------    1775465SQL> select dbms_flashback.get_system_change_number from dual;GET_SYSTEM_CHANGE_NUMBER------------------------                 1775466

删除数据，并提交

SQL> delete flash_tbl where id > 10;10 rows deleted.SQL> commit;Commit complete.

执行select语句并附加as of scn子句，同时指定删除前的scn，就可以查询到指定scn时对象中的记录

SQL> select * from flash_tbl as of scn  1775466;        ID VL---------- --        10 I        11 J        12 K        13 L        14 M        15 N        16 O        17 P        18 Q        19 R        20 S        ID VL---------- --         1 /         2 A         3 B         4 C         5 D         6 E         7 F         8 G         9 H20 rows selected.

执行Insert进行恢复

SQL> insert into flash_tbl     2  select * from flash_tbl as of scn  1775466   3  where id > 10;10 rows created.

其实，Oracle内部都是通过scn来标记操作而不是时间

实际，时间转换后对应具体的scn，映射关系是通过sys.smon_scn_time

oracle 也要两个函数专门用来转换，如下

SQL> select timestamp_to_scn(sysdate) from dual;TIMESTAMP_TO_SCN(SYSDATE)-------------------------                  1775729SQL> select scn_to_timestamp( 1775466) from dual;SCN_TO_TIMESTAMP(1775466)---------------------------------------------------------------------------02-SEP-14 05.06.56.000000000 PM

（3）使用flashback version query查询记录修改版本

在当前时间点和指定的过去时间点之间，对象可能做过多次修改

10g版本之后，通过在select语句之后附加versions between timestamp start ans end子句即可，通过versions between 能够查看指定时间段内undo表空间中记录的不同版本（只包含已提交的记录）

SQL> select dbms_flashback.get_system_change_number from dual;GET_SYSTEM_CHANGE_NUMBER------------------------                 1779487SQL> update flash_tbl set id=id+100 where id>15;5 rows updated.SQL> commit;Commit complete.SQL> delete flash_tbl where id < 5;4 rows deleted.SQL> commit;Commit complete.SQL> insert into flash_tbl values (201,'A1');1 row created.SQL>  insert into flash_tbl values (202,'B1');1 row created.SQL> commit;Commit complete.SQL> select dbms_flashback.get_system_change_number from dual;GET_SYSTEM_CHANGE_NUMBER------------------------                 1779569

执行versions query，就可以看到flash_tbl表中的记录的变化情况了

SQL> select id,vl,versions_startscn,versions_endscn,versions_operation,versions_xid  2  from flash_tbl versions between scn 1779487 and 1779569;        ID VL VERSIONS_STARTSCN VERSIONS_ENDSCN V VERSIONS_XID---------- -- ----------------- --------------- - ----------------         4 C            1779559                 D 09001200D2050000         3 B            1779559                 D 09001200D2050000         2 A            1779559                 D 09001200D2050000         1 /            1779559                 D 09001200D2050000       120 S            1779552                 U 0700040030050000       119 R            1779552                 U 0700040030050000       118 Q            1779552                 U 0700040030050000       117 P            1779552                 U 0700040030050000       116 O            1779552                 U 0700040030050000         1 /                            1779559         2 A                            1779559        ID VL VERSIONS_STARTSCN VERSIONS_ENDSCN V VERSIONS_XID---------- -- ----------------- --------------- - ----------------         3 B                            1779559         4 C                            1779559         5 D         6 E         7 F         8 G         9 H        10 I        11 J        12 K        13 L        ID VL VERSIONS_STARTSCN VERSIONS_ENDSCN V VERSIONS_XID---------- -- ----------------- --------------- - ----------------        14 M        15 N        16 O                            1779552        17 P                            1779552        18 Q                            1779552        19 R                            1779552        20 S                            1779552       202 B1           1779567                 I 0A0019006E040000       201 A1           1779567                 I 0A0019006E04000031 rows selected.

（4）flashback transaction query查询事务信息

该功能对应一个视图flashback_transaction_query

首先删除一条数据,删除前记录scn

SQL> select dbms_flashback.get_system_change_number from dual;GET_SYSTEM_CHANGE_NUMBER------------------------                 1784111SQL> delete flash_tbl where id =7;1 row deleted.SQL> commit;Commit complete.SQL> select dbms_flashback.get_system_change_number from dual;GET_SYSTEM_CHANGE_NUMBER------------------------                 1784124SQL> select xid,commit_scn,commit_timestamp,operation,undo_sql  2  from flashback_transaction_query q where q.xid in (  3  select versions_xid from flash_tbl versions between scn 1784111 and 1784124);XID              COMMIT_SCN COMMIT_TIMES OPERATION---------------- ---------- ------------ --------------------------------UNDO_SQL--------------------------------------------------------------------------------03001D00C0050000    1784122 02-SEP-14    UNKNOWN03001D00C0050000    1784122 02-SEP-14    BEGIN

tip：如果查询5分钟之前的存储过程，可以使用dba_source去查

0 0