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

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

482322 D 04000000CD000000

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博客

Flashback Quary 简单练习

最新文章