空间管理您的位置: ITPUB个人空间 » cqubityj的个人空间 » 日志

flashback query

上一篇 / 下一篇 2007-12-29 15:22:03 / 个人分类：oracle

flashback query分为两种，分别是flashback versions query和flashback transaction query.

flashback versions query有两种用法：
(1) 查看两个时间点/scn之间表记录的所有版本情况:
select * from table_name versions between timestamp/scn...
如果不知道最早和最晚的时间点或scn,可以用minvalue和maxvalue来代替.
(2) 查看某时间点/scn表中记录情况:
select * from table_name as of timestamp/scn...

flashback versions query的虚拟列：
versions_startscn、versions_starttime:开始scn或时间点.如果值为NULL表示创建该版本的时间早于指定的scn或时间点。
versions_endscn、versions_endtime:结束scn或时间点.如果值为NULL表示该版本当前还存在或执行的是delete.
versions_xid:事务ID
versions_operation:操作类型. D=delete、U=update、I=insert

flashback versions query的限制条件：
不能用于查询外部表、临时表和fixed table.
不能用于视图.
无法跨越表结构修改.
支持IOT表，但IOT表的update将分解成delete和insert.

1、flashback versions query简单测试
SQL> create table p (id number, name varchar2(10));

Table created.

SQL> insert into p values (1,'test1');

1 row created.

SQL> insert into p values (2,'test2');

1 row created.

SQL> commit;

Commit complete.

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
8127315560

SQL> insert into p values (3,'test3');

1 row created.

SQL> update p set name='test20' where id=2;

1 row updated.

SQL> commit;

Commit complete.

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
8127315597

SQL> delete from p where id=3;

1 row deleted.

SQL> commit;

Commit complete.

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
8127315640

SQL> select * from p;

        ID NAME
---------- --------------------
         1 test1
         2 test20

SQL> select versions_startscn,versions_endscn,versions_xid,versions_operation,id,name from p versions between scn 8127315560 and 8127315640;

VERSIONS_STARTSCN VERSIONS_ENDSCN VERSIONS_XID     VE         ID NAME
----------------- --------------- ---------------- -- ---------- --------------------
       8127315637                 0100280068020100 D           3 test3
       8127315594                 01001E0069020100 U           2 test20
       8127315594      8127315637 01001E0069020100 I           3 test3
                                                               1 test1
                       8127315594                              2 test2

flashback transaction：
flashback transaction查询flashback_transaction_query得到transaction信息.
使用flashback transaction需要select any transaction系统权限.
如果表中有chained rows或是cluster table,则需要enable supplemental log.
alter database add supplemental log data;
2、flashback transaction测试

SQL> create table p (id number,sal number);

Table created.

SQL> insert into p values (1,100);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from p;

        ID        SAL
---------- ----------
         1        100

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
8138851177

SQL> update p set sal=1000 where id=1;

1 row updated.

SQL> commit;

SQL> select * from p;

        ID        SAL
---------- ----------
         1       1000

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
8138851256

SQL> select versions_startscn,versions_endscn,versions_xid,versions_operation,id,sal from p versions between scn 8138851177 and 8138851256;

VERSIONS_STARTSCN VERSIONS_ENDSCN VERSIONS_XID     VE         ID        SAL
----------------- --------------- ---------------- -- ---------- ----------
       8138851207                 01000E009A020100 U           1       1000
                       8138851207                              1        100

SQL> select start_scn,commit_scn,logon_user,operation,table_name,undo_sql from flashback_transaction_query where xid=hextoraw('&xid');
Enter value for xid: 01000E009A020100
old   1: select start_scn,commit_scn,logon_user,operation,table_name,undo_sql from flashback_transaction_query where xid=hextoraw('&xid')
new   1: select start_scn,commit_scn,logon_user,operation,table_name,undo_sql from flashback_transaction_query where xid=hextoraw('01000E009A020100')
select start_scn,commit_scn,logon_user,operation,table_name,undo_sql from flashback_transaction_query where xid=hextoraw('01000E009A020100')
                                                                          *
ERROR at line 1:
ORA-01031: insufficient privileges
由于没有授权，提示无权限.

SQL> conn /as sysdba
Connected.
SQL> grant select any transaction to user1;

Grant succeeded.

SQL> conn user1/abc
Connected.

SQL> select start_scn,commit_scn,logon_user,operation,table_name,undo_sql from flashback_transaction_query where xid=hextoraw('&xid');
Enter value for xid: 01000E009A020100
old 1: select start_scn,commit_scn,logon_user,operation,table_name,undo_sql from flashback_transaction_query where xid=hextoraw('&xid')
new 1: select start_scn,commit_scn,logon_user,operation,table_name,undo_sql from flashback_transaction_query where xid=hextoraw('01000E009A020100')

START_SCN COMMIT_SCN LOGON_USER OPERATION TABLE_NAME
---------- ---------- ---------- ---------- ----------
UNDO_SQL
------------------------------------------------------------------------------------------------------------------------
8138851171 8138851207 USER1 UPDATE P
update "USER1"."P" set "SAL" = '100' where ROWID = 'AAAPn2AAWAAAAAuAAA';

8138851171 8138851207 USER1 BEGIN

SQL> update "USER1"."P" set "SAL" = '100' where ROWID = 'AAAPn2AAWAAAAAuAAA';

1 row updated.

SQL> select * from p;

ID SAL
---------- ----------
1 100

SQL> commit;

Commit complete.
使用undo_sql回滚update操作.