闪回查询

主要实验了闪回技术

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