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 BYS@bys1>select * from test3; ID NAME ---------- ---------- 2 b 20:22:48 BYS@bys1>select current_scn from v$database; CURRENT_SCN ----------- 1741868 20:26:17 BYS@bys1>delete test3; ---不提交 1 row deleted. 各种查询: 20:26:26 BYS@bys1>select * from test3; no rows selected 20:26:29 BYS@bys1>select current_scn from v$database; CURRENT_SCN ----------- 1741881 20:26:50 BYS@bys1>select * from test3 as of scn 1741868; ID NAME ---------- ---------- 2 b 20:27:10 BYS@bys1>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
一、不得利用本站危害国家安全、泄露国家秘密,不得侵犯国家社会集体的和公民的合法权益,不得利用本站制作、复制和传播不法有害信息!
二、互相尊重,对自己的言论和行为负责。