閃回查詢

主要實驗了閃回技術

a. 開啟補充日誌(當使用閃回事物查詢時需要追加補充日誌)

SQL> alter database add supplemental log data;

Database altered.

b. 建立測試用表SCOTT.TEST_QUERY

drop table scott.test_query;

create table scott.test_query (id number, name varchar2(10), salary number);

--插入員工john, william, lisa的工資資訊

SQL> insert into scott.test_query values(1,'John',5000);

1 row created.

SQL> insert into scott.test_query values(2,'William',6000);

1 row created.

SQL> insert into scott.test_query values(3,'Lisa',7000);

1 row created.

SQL> commit;

Commit complete.

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),CURRENT_SCN from v$database;

TO_CHAR(SYSDATE,'YY CURRENT_SCN

------------------- -----------

2013-03-31 14:32:17 1056363

--插入新員工chris的工資資訊

SQL> insert into scott.test_query values (4,'Chris',4000);

1 row created.

SQL> commit;

Commit complete.

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),CURRENT_SCN from v$database;

TO_CHAR(SYSDATE,'YY CURRENT_SCN

------------------- -----------

2013-03-31 14:32:40 1056383

----模擬一次錯誤的修改操作

SQL> update scott.test_query set salary=0 where name='John';

1 row updated.

SQL> update scott.test_query set salary=10000 where name='Lisa';

1 row updated.

SQL> commit;

Commit complete.

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),CURRENT_SCN from v$database;

TO_CHAR(SYSDATE,'YY CURRENT_SCN

------------------- -----------

2013-03-31 14:33:06 1056404

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),CURRENT_SCN from v$database;

TO_CHAR(SYSDATE,'YY CURRENT_SCN

------------------- -----------

2013-03-31 14:33:22 1056410

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),CURRENT_SCN from v$database;

TO_CHAR(SYSDATE,'YY CURRENT_SCN

------------------- -----------

2013-03-31 14:33:26 1056412

-- 上調工資10%

SQL> update scott.test_query set salary=salary*1.1;

4 rows updated.

SQL> commit;

Commit complete.

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss'),CURRENT_SCN from v$database;

TO_CHAR(SYSDATE,'YY CURRENT_SCN

------------------- -----------

2013-03-31 14:33:46 1056422

--發現資料被錯誤修改

SQL> select * from scott.test_query;

ID NAME SALARY

---------- ---------- ----------

1 John 0

2 William 6600

3 Lisa 11000

4 Chris 4400

c. 嘗試使用閃回查詢確定正確的時間點

SQL> select * from scott.test_query as of timestamp to_date('2013-03-31 14:33:46','yyyy-mm-dd hh24:mi:ss');

ID NAME SALARY

---------- ---------- ----------

1 John 0

2 William 6600

3 Lisa 11000

4 Chris 4400

SQL> select * from scott.test_query as of timestamp to_date('2013-03-31 14:32:40','yyyy-mm-dd hh24:mi:ss');

ID NAME SALARY

---------- ---------- ----------

1 John 5000

2 William 6000

3 Lisa 7000

4 Chris 4000

SQL> select * from scott.test_query as of timestamp to_date('2013-03-31 14:32:17','yyyy-mm-dd hh24:mi:ss');

ID NAME SALARY

---------- ---------- ----------

1 John 5000

2 William 6000

3 Lisa 7000

d. 使用閃回版本查詢確定JOHN工資發生的所有變化

SQL> set line 200

SQL> col VERSIONS_STARTTIME for a25

SQL> col VERSIONS_XID for a20

SQL> col id for 999

SQL> col name for a7

select VERSIONS_STARTTIME,VERSIONS_OPERATION,VERSIONS_XID,id,name,salary

2 from scott.test_query VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE where name='John';

VERSIONS_STARTTIME V VERSIONS_XID ID NAME SALARY

------------------------- - -------------------- ---- ------- ----------

31-MAR-13 02.33.39 PM U 03001E0031030000 1 John 0

31-MAR-13 02.32.57 PM U 08001800F9030000 1 John 0

31-MAR-13 02.32.08 PM I 0300200031030000 1 John 5000

e. 使用閃回事物查詢確定該錯誤執行的事物做的所有變更

SQL> set line 200

SQL> COL UNDO_SQL FOR A40

SQL> COL OPERATION FOR A10

SQL> col name for a20

SQL> col table_name for a20

SQL> SELECT operation, undo_sql, START_SCN,

(select name from scott.test_query where rowid=flashback_transaction_query.ROW_ID) name

FROM flashback_transaction_query

WHERE xid = HEXTORAW('08001800F9030000')

ORDER BY undo_change#;

OPERATION UNDO_SQL START_SCN NAME

---------- ---------------------------------------- ---------- --------------------

UPDATE update "SCOTT"."TEST_QUERY" set "SALARY" 1056386 Lisa

= '7000' where ROWID = 'AAATFYAAEAAAAIX

AAC';

UPDATE update "SCOTT"."TEST_QUERY" set "SALARY" 1056386 John

= '5000' where ROWID = 'AAATFYAAEAAAAIX

AAA';

BEGIN 1056386

SELECT operation, undo_sql, START_SCN,

(select name from scott.test_query where rowid=flashback_transaction_query.ROW_ID) name

FROM flashback_transaction_query

WHERE xid = HEXTORAW('03001E0031030000')

ORDER BY undo_change#;

OPERATION UNDO_SQL START_SCN NAME

---------- ---------------------------------------- ---------- --------------------

UPDATE update "SCOTT"."TEST_QUERY" set "SALARY" 1056416 Chris

= '4000' where ROWID = 'AAATFYAAEAAAAIX

AAD';

UPDATE update "SCOTT"."TEST_QUERY" set "SALARY" 1056416 Lisa

= '10000' where ROWID = 'AAATFYAAEAAAAI

XAAC';

UPDATE update "SCOTT"."TEST_QUERY" set "SALARY" 1056416 William

= '6000' where ROWID = 'AAATFYAAEAAAAIX

AAB';

OPERATION UNDO_SQL START_SCN NAME

---------- ---------------------------------------- ---------- --------------------

UPDATE update "SCOTT"."TEST_QUERY" set "SALARY" 1056416 John

= '0' where ROWID = 'AAATFYAAEAAAAIXAAA

';

BEGIN 1056416

f. 修正資料

update scott.test_query set salary=5500 where name='John';

update scott.test_query set salary=7700 where name='Lisa';

commit;

g.另外也可以使用閃回表回退到錯誤操作發生前的時間點

SQL> alter table scott.test_query enable row movement;

Table altered.

SQL> flashback table scott.test_query to scn 1056383;

Flashback complete.

SQL> select * from scott.test_query;

ID NAME SALARY

---- -------------------- ----------

1 John 5000

2 William 6000

3 Lisa 7000

4 Chris 4000

閃回查詢

相關文章