在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 3:57:54 PM

select rate, versions_starttime, versions_endtime

from rates versions

between timestamp 

to_date('12/1/2003 15:57:54','mm/dd/yyyy hh24:mi:ss')

and to_date('12/1/2003 16:57:55','mm/dd/yyyy hh24:mi:ss')

/

      RATE VERSIONS_STARTTIME     VERSIONS_ENDTIME

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

    1.1011

2. 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

/ 

3. the changes from 3:57:52 PM 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 15:57:52', '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