闪加查询、闪回版本查询、闪回事务查询

1.闪回查询： 步骤是记录当前SCN及时间，然后进行DML操作，提交后使用 timestamp和SCN进行对DML操作之前数据的查询 SQL> set time on; 19:13:57 SQL> insert into test select rownum from dual connect by rownum<=5; 5 rows inserted 19:14:47 SQL> commit; Commit complete 19:14:49 SQL> select dbms_flashback.get_system_change_number from dual; GET_SYSTEM_CHANGE_NUMBER ------------------------                  1362061 19:18:11 SQL> delete  test where a>2; 3 rows deleted 19:18:17 SQL> commit; Commit complete 19:21:56 SQL> select * from test as of timestamp to_timestamp('2013/06/23 19:17:00','yyyy/mm/dd hh24:mi:ss');          A ----------          2          3          4          5          1 19:24:23 SQL> select * from test as of scn 1362061;          A ----------          2          3          4          5          1 2.闪回查询--查询的是已经提交的数据 因为闪回查询的是已经提交的，这样即使数据未提交而数据库SHUTDOWN ABORT，重启后因为做实例恢复，使用闪回所查询的数据仍是已经提交的。 实验过程：查询当前SCN并执行DML操作不提交并查询当前SCN，使用闪回查询功能查询DML操作之前和之后的SCN 结果是：验证闪回查询返回的是已经提交的数据。 20:22:44 [email protected]>select * from test3;         ID NAME ---------- ----------          2 b 20:22:48 [email protected]>select current_scn from v$database; CURRENT_SCN -----------     1741868 20:26:17 [email protected]>delete test3;   ---不提交 1 row deleted. 各种查询： 20:26:26 [email protected]>select * from test3; no rows selected 20:26:29 [email protected]>select current_scn from v$database; CURRENT_SCN -----------     1741881 20:26:50 [email protected]>select * from test3 as of scn 1741868;         ID NAME ---------- ----------          2 b 20:27:10 [email protected]>select * from test3 as of scn 1741881;         ID NAME ---------- ----------          2 b 3.闪回版本查询 versions_operation:    操作类型 versions_xid:    事务编号 versions_starttime:    开始时间 versions_endtime:    结束时间，如果有值，证明这记录已非当前记录 versions_startscn:    开始SCN号 versions_endscn:    结束SCN号 如果有值，证明这记录已非当前记录 操作步骤： 19:25:28 SQL> select dbms_flashback.get_system_change_number from dual; GET_SYSTEM_CHANGE_NUMBER ------------------------                  1362268 19:37:09 SQL> col versions_starttime for a25 19:37:22 SQL> col versions_endtime for a25 19:38:02 SQL> select versions_startscn,versions_starttime, versions_endtime, versions_operation,versions_xid  from  test  versions between  timestampto_timestamp('2013/06/2319:17:00','yyyy/mm/dd hh24:mi:ss')and  to_timestamp('2013/06/2319:20:00','yyyy/mm/dd hh24:mi:ss');   VERSIONS_STARTSCN VERSIONS_STARTTIME        VERSIONS_ENDTIME          VERSIONS_OPERATION VERSIONS_XID ----------------- ------------------------- ------------------------- ------------------ ----------------           1362122 23-JUN-13 07.19.48 PM                               D                  04001200C8020000           1362122 23-JUN-13 07.19.48 PM                               D                  04001200C8020000           1362122 23-JUN-13 07.19.48 PM                               D                  04001200C8020000                                             23-JUN-13 07.19.48 PM                                                                     23-JUN-13 07.19.48 PM                                                                     23-JUN-13 07.19.48 PM                         8 rows selected 4.闪回事务查询 闪回事务查询需要打开追加日志，不然无法从 flashback_transaction_query查出UNDO_SQL 20:35:42 SQL> show parameter undo NAME                                 TYPE        VALUE ------------------------------------ ----------- ------------------------------ undo_management                      string      AUTO undo_retention                       integer     900 undo_tablespace                      string      UNDOTBS1 20:35:57 SQL> alter database add supplemental log data; Database altered. 20:38:01 SQL> select * from test;          A ----------          2          3          1 20:38:24 SQL> update test set a=a+10; 3 rows updated. 20:38:36 SQL> commit; Commit complete. 20:39:37 SQL> col versions_endtime for a20 20:40:25 SQL> col versions_starttime for a20 20:40:36 SQL> set pagesize 100 20:41:56 SQL> select  versions_startscn,versions_starttime, versions_endtime, versions_operation,versions_xid  from test versions between  timestamp to_timestamp('2013/06/23 20:38:00','yyyy/mm/dd hh24:mi:ss') and to_timestamp('2013/06/23 20:39:00','yyyy/mm/dd hh24:mi:ss'); VERSIONS_STARTSCN VERSIONS_STARTTIME   VERSIONS_ENDTIME     V VERSIONS_XID ----------------- -------------------- -------------------- - ----------------           1369588 23-JUN-13 08.38.41 PM                      U 060001003A030000           1369588 23-JUN-13 08.38.41 PM                      U 060001003A030000           1369588 23-JUN-13 08.38.41 PM                      U 060001003A030000                                                         23-JUN-13 08.38.41 PM                                        23-JUN-13 08.38.41 PM                                        23-JUN-13 08.38.41 PM 6 rows selected. 20:41:57 SQL> col operation for a10 20:43:07 SQL> col undo_sql for a60 20:43:20 SQL> select operation,undo_sql  from flashback_transaction_querywhere logon_user='BYS'  andundo_sql like '%TEST%'; OPERATION  UNDO_SQL ---------- ------------------------------------------------------------ UPDATE     update "BYS"."TEST" set "A" = '1' where ROWID = 'AAASYyAAEAA            AAcjAKR'; UPDATE     update "BYS"."TEST" set "A" = '3' where ROWID = 'AAASYyAAEAA            AAcjAAE'; UPDATE     update "BYS"."TEST" set "A" = '2' where ROWID = 'AAASYyAAEAA            AAcjAAA'; 20:44:12 SQL> select operation,undo_sql  from flashback_transaction_query where logon_user='BYS'  and undo_sql like '%TEST%' andxid=HEXTORAW('060001003A030000'); OPERATION  UNDO_SQL ---------- ------------------------------------------------------------ UPDATE     update "BYS"."TEST" set "A" = '1' where ROWID = 'AAASYyAAEAA            AAcjAKR'; UPDATE     update "BYS"."TEST" set "A" = '3' where ROWID = 'AAASYyAAEAA            AAcjAAE'; UPDATE     update "BYS"."TEST" set "A" = '2' where ROWID = 'AAASYyAAEAA            AAcjAAA'; 可以使用XID做为条件 ： 20:45:01 SQL> select operation,undo_sql  from flashback_transaction_querywhere  xid=HEXTORAW('060001003A030000'); OPERATION  UNDO_SQL ---------- ------------------------------------------------------------ UPDATE     update "BYS"."TEST" set "A" = '1' where ROWID = 'AAASYyAAEAA            AAcjAKR'; UPDATE     update "BYS"."TEST" set "A" = '3' where ROWID = 'AAASYyAAEAA            AAcjAAE'; UPDATE     update "BYS"."TEST" set "A" = '2' where ROWID = 'AAASYyAAEAA            AAcjAAA'; BEGIN 可以使用XID做为条件 ： 20:45:17 SQL> select operation,undo_sql  from flashback_transaction_querywhere  xid='060001003A030000'; OPERATION  UNDO_SQL ---------- ------------------------------------------------------------ UPDATE     update "BYS"."TEST" set "A" = '1' where ROWID = 'AAASYyAAEAA            AAcjAKR'; UPDATE     update "BYS"."TEST" set "A" = '3' where ROWID = 'AAASYyAAEAA            AAcjAAE'; UPDATE     update "BYS"."TEST" set "A" = '2' where ROWID = 'AAASYyAAEAA            AAcjAAA'; BEGIN