Oracle10g New Feature -- 2.Flashback Records

在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:

 

 

 

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

 

2. also use the SCN to find the value of a version in the past

3. the changes from 3:57:52 PM only; not the complete range

 

 

 

 

 

 

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

转载于:http://blog.itpub.net/207/viewspace-778618/