ITPub博客

首页 > 数据库 > Oracle > Oracle10g New Feature -- 2.Flashback Records

Oracle10g New Feature -- 2.Flashback Records

原创 Oracle 作者:zhyuh 时间:2004-09-15 17:13:49 0 删除 编辑

在oracle10g中,用户可以查询纪录在各时间点上的值,和每次对该纪录的DML操作。

但是是不是在任何时候都可以查到从该纪录产生起的所有更改历史,还是有时间跨度的限制?Oracle为此功能多消耗多少空间?目前还是不太明白。

另外《Oracle Database 10g Top 20 Features for the DBA》(http://www.oracle.com/technology/pub/articles/10gdba/week1_10gdba.html) 中,对FLASHBACK_TRANSACTION_QUERY.UNDO_SQL的理解和引用例子中有错,作笔记时作了改正。

[@more@]

1.    Flashback Versions Query

Example:

SQL> desc rates
 Name              Null?    Type
 ----------------- -------- ------------
 CURRENCY                   VARCHAR2(4)
 RATE                       NUMBER(15,10)

 

SQL> insert into rates values ('EURO',1.1012);
SQL> commit;
SQL> update rates set rate = 1.1014;
SQL> commit;
SQL> update rates set rate = 1.1013;
SQL> commit;
SQL> delete rates;
SQL> commit;
SQL> insert into rates values ('EURO',1.1016);
SQL> commit;
SQL> update rates set rate = 1.1011;
SQL> commit;

 

SQL> select * from rates;
CURR       RATE
---- ----------
EURO     1.1011

 

The following query shows the changes made to the table

select versions_starttime, versions_endtime, versions_xid,

versions_operation, rate

from rates versions between timestamp minvalue and maxvalue

order by VERSIONS_STARTTIME

/

VERSIONS_STARTTIME     VERSIONS_ENDTIME       VERSIONS_XID     V       RATE

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

01-DEC-03 03.57.12 PM  01-DEC-03 03.57.30 PM  0002002800000C61 I     1.1012

01-DEC-03 03.57.30 PM  01-DEC-03 03.57.39 PM  000A000A00000029 U     1.1014

01-DEC-03 03.57.39 PM  01-DEC-03 03.57.55 PM  000A000B00000029 U     1.1013

01-DEC-03 03.57.55 PM                         000A000C00000029 D     1.1013

01-DEC-03 03.58.07 PM  01-DEC-03 03.58.17 PM  000A000D00000029 I     1.1016

01-DEC-03 03.58.17 PM                         000A000E00000029 U     1.1011

VERSIONS_OPERATION column shows what operation (Insert/Update/Delete) was performed on the row,

VERSIONS_XID shows the identifier of the transaction that changed the row.

VERSIONS_STARTTIME, VERSIONS_ENDTIME, VERSIONS_XID, VERSIONS_OPERATION are pseudo-columns, similar to other familiar ones such as ROWNUM, LEVEL.

Other pseudo-columns—such as VERSIONS_STARTSCN and VERSIONS_ENDSCN—show the System Change Numbers at that time.

using the VERSIONS_XID value 000A000D00000029 from above, the UNDO_SQL value shows SQL to undo the DML indicated by OPERATION:

SELECT UNDO_SQL

FROM FLASHBACK_TRANSACTION_QUERY

WHERE XID = '000A000D00000029';

UNDO_SQL

Delete from “ANANDA”.”RATES” where ROWID = 'AAAMj2AAEAAAAFtAAA'

Finding Out Changes During a Period

  1. find out the value of the RATE column at

select rate, versions_starttime, versions_endtime
from rates versions
between timestamp 
to_date('12/1/2003 ','mm/dd/yyyy hh24:mi:ss')
and to_date('12/1/2003 ','mm/dd/yyyy hh24:mi:ss')
/

 

      RATE VERSIONS_STARTTIME     VERSIONS_ENDTIME
---------- ---------------------- ----------------------
    1.1011
  1. also use the SCN to find the value of a version in the past

select rate, versions_starttime, versions_endtime
from rates versions
between scn 1000 and 1001
/ 
  1. the changes from only; not the complete range

select versions_starttime, versions_endtime, versions_xid, 
versions_operation, rate 
from rates versions between timestamp 
to_date('12/11/2003 ', 'mm/dd/yyyy hh24:mi:ss')
and maxvalue
order by VERSIONS_STARTTIME
/

 

VERSIONS_STARTTIME     VERSIONS_ENDTIME       VERSIONS_XID     V       RATE
---------------------- ---------------------- ---------------- - ----------
01-DEC-03 03.57.55 PM                         000A000C00000029 D     1.1013
01-DEC-03 03.58.07 PM  01-DEC-03 03.58.17 PM  000A000D00000029 I     1.1016
01-DEC-03 03.58.17 PM                         000A000E00000029 U     1.1011

 

 

 

 

 

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

上一篇: CMM5
请登录后发表评论 登录
全部评论
  • 博文量
    233
  • 访问量
    2009598