Oracle的flashback特性之一：Flashback Query

Flashback特性flashback query、flashback table、flashbackdatabase依赖Undo表空间，必须启用Undo表空间管理。

flashback table  需要启用回收站recycle bin。

flashbackdatabase 需要启用闪回区flashback area。

SQL> show parameter undo;

NAME                                 TYPE
------------------------------------ ----------------------
VALUE
------------------------------
undo_management                      string
AUTO
undo_retention                       integer
900
undo_tablespace                      string
UNDOTBS1 

undo_management   值为auto表示自动管理，manual表示手动管理。

undo_tablespace    当undo_management值为auto时，用来指定当前undo表空间名称。undo表空间大小直接影响查询能力。

undo_retention      记录undo记录保存的最长时间，单位秒，默认900秒，动态参数。undo_retention 只是指定数据的过期时间，并不是说undo数据一定会在undo表空间中保存15分钟，如一个新事务开始时，undo空间已经写满，新事务数据会覆盖先前已提交的数据，而不管这些数据是否已经过期；另外也不是说时间一过，已提交事务的数据就立刻无法访问，它只是失效，只要不被其他事务覆盖，仍然存在，可以被使用。

有一种特殊情况，能够确保undo中的数据在undo_retention指定时间过期前一定有效，不会覆盖未过期的数据。

启用：alter tablespace UNDOTBS1retention guarantee;

禁止：alter tablespace UNDOTBS1retention noguarantee;

需要注意事务提交之后，对表做了DDL操作，会是undo中的撤销数据失效，应用flashback query会触发错误。另外表结构修改并不影响undo中的撤销记录，但有可能导致undo记录无法应用，例如增加了约束。flashback query对v$tables，v$tables等动态性能视图无效，dba_*，all_*，user_*等数据字典有效，可以支持远程数据库访问，select* from test@dblink as of scn 54321。

一、闪回查询Flashback Query

1、基于事件，as of timestamp

（1）测试表。

SQL> select * from test1;

        ID NAME
---------- --------------------
         1 aa
         2 bb
         3 cc
         4 dd
         5 ee

（2）删除掉其中几条。

SQL> delete from test1 where id > 3;

2 rows deleted.

SQL> commit;

Commit complete.

SQL> select * from test1;

        ID NAME
---------- --------------------
         1 aa
         2 bb
         3 cc

（3）闪回查询

SQL> select * from test1 as of timestamp sysdate - 2/1440;

        ID NAME
---------- --------------------
         1 aa
         2 bb
         3 cc
         4 dd
         5 ee

（4）恢复数据。

SQL> insert into test1 select * from test1 as of timestamp sysdate - 2/1440 where id >3;

2 rows created.

SQL> commit;

Commit complete.

SQL> select * from test1;

        ID NAME
---------- --------------------
         1 aa
         2 bb
         3 cc
         4 dd
         5 ee

2、基于scn， as of scn，如果对相互有主外键约束的表进行恢复时，as of timestamp方式可能会由于时间点不统一，造成数据选择或插入失败，scn方式能够确保记录的约束一致性。

（1）获取当前scn。

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
     500381

（2）删除数据。

SQL> delete from test1 where id >3;

2 rows deleted.

SQL> commit;

Commit complete.

（3）闪回查询。

SQL> select * from test1 as of scn 500381;

        ID NAME
---------- --------------------
         1 aa
         2 bb
         3 cc
         4 dd
         5 ee

（4）恢复数据。

SQL> insert into test1 select * from test1 as of scn 500381 where id > 3;

2 rows created.

SQL> commit;

Commit complete.

SQL> select * from test1;

        ID NAME
---------- --------------------
         1 aa
         2 bb
         3 cc
         4 dd
         5 ee

3、版本查询，versions between，能够查看指定时间段内undo表空间中记录的不同版本，只包括已经提交的记录。

（1）获取当前scn

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
     512219

（2）一系列提交操作

SQL> update test1 set id = id + 2 where id >3;

2 rows updated.

SQL> commit;

Commit complete.

SQL> delete from test1 where id < 3;

2 rows deleted.

SQL> commit;

Commit complete.

SQL> insert into test1 values(6, 'ff');

1 row created.

SQL> insert into test1 values(7, 'gg');

1 row created.

SQL> commit;

Commit complete.

（3）获取当前scn

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
     512333

（4）查询数据

SQL> select id, name, versions_startscn, versions_endscn, versions_operation from test1 versions between scn 512219 and 512333 order by 2;

        ID NAME                 VERSIONS_STARTSCN VERSIONS_ENDSCN VE
---------- -------------------- ----------------- --------------- --
         1 aa                              512276                 D
         1 aa                                              512276
         2 bb                                              512276
         2 bb                              512276                 D
         3 cc
         6 dd                              512258                 U
         4 dd                                              512258
         5 ee                                              512258
         7 ee                              512258                 U
         6 ff                              512291                 I
         7 gg                              512291                 I

11 rows selected.

（5）结果分析

versions_operation：对该行执行的操作：I表示INSERT，D表示DELETE，U表示UPDATE，对于索引键的UPDATE操作，版本查询可能会将其识别成两个操作：DELETE和INSERT。如果为D，表明该列已被删除，如果该列为空，表明记录在这段时间误操作。

versions_startscn/versions_starttime：该记录操作的scn或时间，如果为空，表示该行记录是在查询范围外创建的。

versinons_endscn/versions_endtime：该记录失效的scn或时间，如果为空，说明记录当前时间在当前表内存在，或者已经被删除。

4、事务查询，transaction query

（1）当前scn

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
     513490

（2）提交操作

SQL> delete from test1 where id < 3;

0 rows deleted.

SQL> rollback;

Rollback complete.

SQL> delete from test1 where id = 3;

1 row deleted.

SQL> commit;

Commit complete.

（3）当前scn

SQL>  select current_scn from v$database;

CURRENT_SCN
-----------
     513545

（4）查询事物

SQL> select xid, commit_scn, commit_timestamp, operation, undo_sql from flashback_transaction_query q 
where q.xid in(select versions_xid from scott.test1 versions between scn 513490 and 513545);

XID              COMMIT_SCN COMMIT_TIMESTAMP
---------------- ---------- -------------------
OPERATION
----------------------------------------------------------------
UNDO_SQL
--------------------------------------------------------------------------------
050019002C010000     513533 2012-10-27 11:33:15
DELETE
insert into "SCOTT"."TEST1"("ID","NAME") values ('3','cc');

050019002C010000     513533 2012-10-27 11:33:15
BEGIN

（5）结果分析

上述查询列出了前面所做的操作和时间，实际执行的语句该视图并没有记录，只能通过undo_sql和operation推测，实际使用中，该视图存储记录量较大，查询的时候建议通过关键列过滤，比如logon_user，table_name，table_owner等。