在oracle10g中，使用者可以查詢紀錄在各時間點上的值，和每次對該紀錄的DML操作。

但是是不是在任何時候都可以查到從該紀錄產生起的所有更改歷史，還是有時間跨度的限制？Oracle為此功能多消耗多少空間？目前還是不太明白。

另外《Oracle Database 10g Top 20 Features for the DBA》() 中，對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'

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

select rate, versions_starttime, versions_endtime

from rates versions

between scn 1000 and 1001

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

Oracle10g New Feature -- 2.Flashback Records

相關文章