ITPub博客

首页 > 数据库 > Oracle > Flashback Quary 简单练习

Flashback Quary 简单练习

原创 Oracle 作者:静以致远√团团 时间:2014-03-19 14:27:28 0 删除 编辑

Flashback Quary   闪回查询 

 

基于时间的闪回查询:

查看测试数据:

SQL> conn scott/oracle

Connected.

 

SQL> select * from scott.test_tab;

ID

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

FL_TEST_1

FL_TEST_2

FL_TEST_3

FL_TEST_4

FL_TEST_5

删除一行数据:

SQL> delete from test_tab where rownum<2;

1 row deleted.

 

SQL> commit;

Commit complete.

 

SQL> select * from test_tab;

ID

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

FL_TEST_2

FL_TEST_3

FL_TEST_4

FL_TEST_5

 

查看两分钟前的闪回数据:

SQL> select * from TEST_TAB as of timestamp sysdate-2/(60*24);

ID

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

FL_TEST_1

FL_TEST_2

FL_TEST_3

FL_TEST_4

FL_TEST_5

 

将删除的数据插入表中:

SQL> insert into test_tab 

  2  select * from TEST_TAB as of timestamp sysdate-2/(60*24) 

  3  where rownum < 2

  4  /

1 row 

 

SQL> commit;

Commit complete.created.

 

SQL> select * from test_tab;

ID

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

FL_TEST_1

FL_TEST_2

FL_TEST_3

FL_TEST_4

FL_TEST_5

 

基于SCN的闪回查询:

赋予scott用户查询闪回SCN的权限:(也可以授予v$database视图的查看权限,来查看SCN

SQL> conn / as sysdba

Connected.

 

SQL> grant execute on dbms_flashback to scott;

Grant succeeded.

或者

SQL> grant select on v_$database to scott;

Grant succeeded.

 

查看当前的SCN

SQL> conn scott/oracle

Connected.

 

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER

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

                  480115

或者

 

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

     480118

 

删除数据进行SCN查询恢复:

SQL> delete from test_tab;

5 rows deleted.

 

SQL> select * from test_tab;

no rows selected

 

SQL> select * from test_tab as of scn 480115;

ID

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

FL_TEST_1

FL_TEST_2

FL_TEST_3

FL_TEST_4

FL_TEST_5

 

将删除数据添加到原表:

SQL> insert into test_tab

  2  select * from test_tab as of scn 480115

  3  /

5 rows created.

 

SQL> commit;

Commit complete.

 

SQL> select * from test_tab;

ID

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

FL_TEST_1

FL_TEST_2

FL_TEST_3

FL_TEST_4

FL_TEST_5

 

查看某一时间的SCN

查看五分钟前时刻对应的SCN

SQL> select timestamp_to_scn(sysdate-5/1440) from dual;

TIMESTAMP_TO_SCN(SYSDATE-5/1440)

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

                          480956

查看某一SCN对应的时间:

SQL> select scn_to_timestamp(481079) from dual;

SCN_TO_TIMESTAMP(481079)

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

18-MAR-14 10.04.35.000000000 AM

提示:可以根据SYS.SCN_SCN_TIME视图查看数据库中的最小SCN

 

FlashBack查询操作的事务

 

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER

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

                  482292

SQL> delete from test_tab where rownum < 2;

1 row deleted.

 

SQL> delete from test_tab where rownum < 2;

1 row deleted.

 

SQL> commit;

Commit complete.

 

SQL> select * from test_tab;

ID

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

FL_TEST_3

FL_TEST_4

FL_TEST_5

 

 

SQL> insert into test_tab 

  2  values('FL_TEST_1')

  3  /

1 row created.

 

SQL> insert into test_tab 

  2  values('FL_TEST_1')

  3  /

1 row created.

 

SQL> commit;

Commit complete.

 

SQL> select * from test_tab;

ID

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

FL_TEST_2

FL_TEST_4

FL_TEST_5

FL_TEST_1

FL_TEST_1

 

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER

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

                  482398

利用闪回查询中的Version Quary伪列来查看TEST_TAB表的操作记录

SQL> select versions_startscn,versions_endscn,versions_operation,versions_xid from test_tab versions between scn 482292 and 482398;

 

   VERSIONS_STARTSCN VERSIONS_ENDSCN V VERSIONS_XID

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

              482374                 U 01002300CA000000

              482374                 I 01002300CA000000

              482374                 I 01002300CA000000

              482322                 D 04000000CD000000

              482322                 D 04000000CD000000

                              482322

                              482322

                              482374

10 rows selected.

 

其中:

VERSIONS_STARTSCN(也可以显示VERSIONS_STARTTIME列)为操作开始时的SCN

VERSIONS_ENDSCN(也可以显示VERSIONS_ENDTIME列)为操作结束的SCN

versions_operation 为执行的操作:

I表示insert 操作

U表示uupdate操作

D表示delete操作

 

利用 flashback_transaction_query视图查看事务信息

 

SQL> desc flashback_transaction_query;

 Name                                      Null?    Type

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

 XID                                                RAW(8)

 START_SCN                                          NUMBER

 START_TIMESTAMP                                    DATE

 COMMIT_SCN                                         NUMBER

 COMMIT_TIMESTAMP                                   DATE

 LOGON_USER                                         VARCHAR2(30)

 UNDO_CHANGE#                                       NUMBER

 OPERATION                                          VARCHAR2(32)

 TABLE_NAME                                         VARCHAR2(256)

 TABLE_OWNER                                        VARCHAR2(32)

 ROW_ID                                             VARCHAR2(19)

 UNDO_SQL                                           VARCHAR2(4000)

 

SQL> select * from test_tab;

        ID TNUM

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

         1 FL_TEST_1

         2 FL_TEST_2

         3 FL_TEST_3

         4 FL_TEST_4

         5 FL_TEST_5

 

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER

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

                  483563

 

SQL> delete from test_tab where id = 5;

1 row deleted.

 

SQL> commit;

Commit complete.

 

SQL> select dbms_flashback.get_system_change_number from dual;

GET_SYSTEM_CHANGE_NUMBER

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

                  483739

授予用户相应的查询权限:

SQL> conn / as sysdba

Connected.

 

SQL> grant select on flashback_transaction_query to scott;

Grant succeeded.

 

SQL> grant select any transaction to scott;

Grant succeeded.

 

SQL> conn scott/oracle

Connected.

 

查看事务信息

SQL> select xid,commit_scn,commit_timestamp,operation,undo_sql

  2  from flashback_transaction_query Q where Q.xid in(

  3  select versions_xid from test_tab versions between scn 483563 and 483739)

  4  /

 

XID              COMMIT_SCN COMMIT_TI OPERATION     UNDO_SQL

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

04000700CE000000     483650 18-MAR-14 DELETE        insert into                   "SCOTT"."TEST_TAB"

                                                     ("ID","TNUM") values ('5','FL_

                                                     TEST_5');

04000700CE000000     483650 18-MAR-14 BEGIN

 

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29320885/viewspace-1124901/,如需转载,请注明出处,否则将追究法律责任。

下一篇: Flashback Table
请登录后发表评论 登录
全部评论
每个人都有梦想,去实现吧!

注册时间:2013-11-14

  • 博文量
    164
  • 访问量
    2103649