Oracle10g flashback系列新特性之
flashback version qurey和flash transaction qurey
在Oracle9i中,我们可以可以利用Flash Query从undo segment查看以前时间的列值。然而,Flashback Query仅提供了以前时间的一个快照,而不能在两个不同的时间点之间进行比较处理,有些甚至要求得到某个时间段数据的变化情况。在10g中Flash version query将能简单高效地执行这些工作。不过对象在undo段中的存在时间跟UNDO_RETENTION参数有着直接的关系。
SQL> create table tt(no number,name varchar2(10));
表已创建。
SQL> insert into tt values(1,'ICEMAN');
已创建 1 行。
SQL> commit;
提交完成。
SQL> update tt set name='BONNIE';
已更新 1 行。
SQL> commit;
提交完成。
SQL> update tt set name='BON_ICE';
已更新 1 行。
SQL> commit;
提交完成。
SQL> delete from tt;
已删除 1 行。
SQL> commit;
提交完成。
SQL> insert into tt values(2,'HAHA');
已创建 1 行。
SQL> commit;
提交完成。
SQL> col versions_starttime format a25
SQL> col versions_endtime format a25
SQL> col versions_xid format a16
SQL> select versions_starttime,versions_endtime,versions_xid,versions_operation,
2 name from tt versions between timestamp minvalue and maxvalue
3 order by versions_starttime;
VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID V
------------------------- ------------------------- ---------------- -
NAME
----------
15-5月 -07 10.46.12 下午 15-5月 -07 10.46.54 下午 06001900FA010000 I
ICEMAN
15-5月 -07 10.46.54 下午 15-5月 -07 10.47.12 下午 0A002700F9010000 U
BONNIE
15-5月 -07 10.47.12 下午 15-5月 -07 10.47.27 下午 04000000F7010000 U
BON_ICE
15-5月 -07 10.47.27 下午 03001400F7010000 D
BON_ICE
15-5月 -07 10.49.33 下午 07002800FC010000 I
HAHA
过一段时间我们查一下:
SQL> select versions_starttime,versions_endtime,versions_xid,versions_operation,
2 name from tt versions between timestamp minvalue and maxvalue
3 order by versions_starttime;
VERSIONS_STARTTIME VERSIONS_ENDTIME VERSIONS_XID V
------------------------- ------------------------- ---------------- -
NAME
----------
HAHA
只有现在的一条记录,说明UNDO中的信息已经不存在了,估计是UNDO_RETENTION的值设置太小了。
SQL> show parameter undo_retention
NAME TYPE VALUE
------------------------------------ ----------- ----------------
undo_retention integer 900
把UNDO_RETENTION设置大一点,再做一下前面的插入操作。
SQL> select versions_startscn,versions_endscn,versions_xid,versions_operation,
2 name from tt versions between scn minvalue and maxvalue
3 order by versions_startscn;
VERSIONS_STARTSCN VERSIONS_ENDSCN VERSIONS_XID V NAME
----------------- --------------- ---------------- - ----------
1253506 1253517 04000200F8010000 I ICEMAN
1253517 1253524 05001800FA010000 U BONNIE
1253524 1253530 03000A00F8010000 U BON_ICE
1253530 07002300FD010000 D BON_ICE
1253535 08000600F9010000 I HAHA
我们也可以指定一个时间段段进行查询:
SQL> select name, versions_starttime, versions_endtime,versions_operation
2 from tt versions between timestamp
3 to_date('2007-05-15 23:10:00','yyyy-mm-dd hh24:mi:ss') and to_date('2007-05
-15 23:15:00','yyyy-mm-dd hh24:mi:ss')
4 order by versions_starttime;
NAME VERSIONS_STARTTIME VERSIONS_ENDTIME V
---------- ------------------------- ------------------------- -
BON_ICE 15-5月 -07 11.09.59 下午 15-5月 -07 11.10.11 下午 U
BON_ICE 15-5月 -07 11.10.11 下午 D
HAHA 15-5月 -07 11.10.20 下午 I
BONNIE 15-5月 -07 11.09.59 下午
或者指定一个SCN段:
SQL> select name, versions_startscn, versions_endscn,versions_operation
2 from tt versions between scn 1253517 and 1253535
3 order by versions_startscn;
NAME VERSIONS_STARTSCN VERSIONS_ENDSCN V
---------- ----------------- --------------- -
BONNIE 1253517 1253524 U
BON_ICE 1253524 1253530 U
BON_ICE 1253530 D
HAHA 1253535 I
ICEMAN 2.8147E+14
也可以通过使用minvalue和maxvalue:
SQL> select name, versions_startscn, versions_endscn,versions_operation
2 from tt versions between scn 1253517 and maxvalue
3 order by versions_startscn;
NAME VERSIONS_STARTSCN VERSIONS_ENDSCN V
---------- ----------------- --------------- -
BONNIE 1253517 1253524 U
BON_ICE 1253524 1253530 U
BON_ICE 1253530 D
HAHA 1253535 I
ICEMAN 2.8147E+14
SQL> select name, versions_startscn, versions_endscn,versions_operation
2 from tt versions between scn minvalue and 1253530
3 order by versions_startscn;
NAME VERSIONS_STARTSCN VERSIONS_ENDSCN V
---------- ----------------- --------------- -
ICEMAN 1253506 1253517 I
BONNIE 1253517 1253524 U
BON_ICE 1253524 1253530 U
BON_ICE 1253530 D
SQL> select name, versions_starttime, versions_endtime,versions_operation
2 from tt versions between timestamp
3 to_date('2007-05-15 23:10:00','yyyy-mm-dd hh24:mi:ss') and maxvalue
4 order by versions_starttime;
NAME VERSIONS_STARTTIME VERSIONS_ENDTIME V
---------- ------------------------- ------------------------- -
BON_ICE 15-5月 -07 11.09.59 下午 15-5月 -07 11.10.11 下午 U
BON_ICE 15-5月 -07 11.10.11 下午 D
HAHA 15-5月 -07 11.10.20 下午 I
BONNIE 15-5月 -07 11.09.59 下午
在上面的查询中,
VERSIONS_STARTTIME,VERSIONS_ENDTIME,VERSIONS_STARTSCN,VERSIONS_ENDSCN,
VERSIONS_XID,VERSIONS_OPERATION是类似ROWNUM, LEVEL的伪列。
其中VERSIONS_STARTTIME,VERSIONS_ENDTIME,VERSIONS_STARTSCN,VERSIONS_ENDSCN是取得修改发生时的时间或者SCN。 VERSIONS_XID 是修改事务的标识号
可在视图FLASHBACK_TRANSACTION_QUERY中有得到更多的信息,如下:
SQL> insert into tt values(2,'HAHAHA');
已创建 1 行。
SQL> insert into tt values(3,'HAHAHA');
已创建 1 行。
SQL> insert into tt values(4,'HAHAHA');
已创建 1 行。
SQL> insert into tt values(5,'HAHAHA');
已创建 1 行。
SQL> COMMIT;
提交完成。
SQL> select versions_startscn,versions_endscn,versions_xid,versions_operation,
2 name from tt versions between scn minvalue and maxvalue
3 order by versions_startscn;
VERSIONS_STARTSCN VERSIONS_ENDSCN VERSIONS_XID V NAME
----------------- --------------- ---------------- - ----------
1256606 03002600F8010000 I HAHAHA
1256727 02001E003C020000 I HAHAHA
1256727 02001E003C020000 I HAHAHA
1256727 02001E003C020000 I HAHAHA
HAHA
SQL> SELECT UNDO_SQL
2 FROM FLASHBACK_TRANSACTION_QUERY
3 WHERE XID = '02001E003C020000' or XID = '03002600F8010000';
UNDO_SQL
----------------------------------------------------------------
delete from "SYS"."TT" where ROWID = 'AAAMsdAABAAAO5iAAE';
delete from "SYS"."TT" where ROWID = 'AAAMsdAABAAAO5iAAD';
delete from "SYS"."TT" where ROWID = 'AAAMsdAABAAAO5iAAA';
delete from "SYS"."TT" where ROWID = 'AAAMsdAABAAAO5iAAC';
这些语句对应了上面我们所做的一些插入操作,如果想回滚这些信息,可以执行上面的undo_sql。
SQL> desc FLASHBACK_TRANSACTION_QUERY;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
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 operation,undo_sql
2 from FLASHBACK_TRANSACTION_QUERY
3 where table_name='TT';
OPERATION UNDO_SQL
-------------------------------------------------------------------------------
INSERT delete from "SYS"."TT" where ROWID = 'AAAMsdAABAAAO5iAAE';
INSERT delete from "SYS"."TT" where ROWID = 'AAAMsdAABAAAO5iAAD';
INSERT delete from "SYS"."TT" where ROWID = 'AAAMsdAABAAAO5iAAA';
INSERT delete from "SYS"."TT" where ROWID = 'AAAMsdAABAAAO5iAAC';
UPDATE update "SYS"."TT" set "NAME" = 'BONNIE' where ROWID = 'AAAMsdAABAAAO5iAAA';
INSERT delete from "SYS"."TT" where ROWID = 'AAAMsdAABAAAO5iAAA';
UPDATE update "SYS"."TT" set "NAME" = 'ICEMAN' where ROWID = 'AAAMsdAABAAAO5iAAA';
DELETE insert into "SYS"."TT"("NO","NAME") values ('1','BON_ICE');
INSERT delete from "SYS"."TT" where ROWID = 'AAAMsdAABAAAO5iAAB';
已选择9行。