Oracle10g New Feature -- 2.Flashback Records

zhyuh發表於2004-09-15
原文網址 : http://blog.itpub.net/207/viewspace-778618/
Oracle

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

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

 

 

 

 

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/207/viewspace-778618/，如需轉載，請註明出處，否則將追究法律責任。

相關文章