使用FLASHBACK_TRANSACTION_QUERY查詢回滾事務SQL

提供某時刻資料的固定快照，而不是在兩個時間點之間被更改資料的執行狀態表示。某些應用程式，如涉及到外幣管理的應用程式，可能需要了解一段時期內數值資料的變化，而不僅僅是兩個時間點的數值。由於閃回版本查詢特性，Oracle Database 11g 能夠更方便高效地執行該任務。

查詢對錶的更改

在本示例中，我使用了一個銀行外幣管理應用程式。其資料庫含有一個名稱為 RATES 的表，用於記錄特定時間的匯率。

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

該表顯示 US$ 與各種其他貨幣的匯率，在 CURRENCY 列中顯示。在金融服務行業中，匯率不但在變更時進行更新，而且被記錄在歷史中。需要這種方式的原因是銀行交易可能在“過去時間”生效，以便適應由於匯款而耗費的時間。例如，對於一項在上午 10:12 發生但在上午 9:12 生效的交易，其有效匯率是上午 9:12 的匯率，而不是現在的匯率。

直到現在，唯一的選擇是建立一個匯率歷史表來儲存匯率的變更，然後查詢該表是否提供歷史記錄。另一種選擇是在 RATES 表本身中記錄特定匯率適用性的開始和結束時間。當發生變更時，現有行中的 END_TIME 列被更新為 SYSDATE，並插入一個具有新匯率的新行，其 END_TIME 為 NULL。

但是在 Oracle Database 10g 中，閃回版本查詢特性不需要維護歷史表或儲存開始和結束時間。使用該特性，您不必進行額外的設定，即可獲得某行在過去特定時間的值。

例如，假定該 DBA 在正常業務過程中數次更新匯率 — 甚至刪除了某行並重新插入該行：

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

在進行了這一系列操作後，DBA 將通過以下命令獲得 RATE 列的當前提交值

SQL> select * from rates;

CURR       RATE
---- ----------
EURO     1.1011
此輸出顯示 RATE 的當前值，沒有顯示從第一次建立該行以來發生的所有變更。這時使用閃回查詢，您可以找出給定時間點的值；但我們對構建變更的審計線索更感興趣 — 有些類似於通過行動式攝像機來記錄變更，而不只是在特定點拍攝一系列快照。 
以下查詢顯示了對錶所做的更改： 
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
注意，此處顯示了對該行所作的所有更改，甚至包括該行被刪除和重新插入的情況。VERSION_OPERATION 列顯示對該行執行了什麼操作 (Insert/Update/Delete)。所做的這些工作不需要歷史表或額外的列。 
在上述查詢中，列 versions_starttime、versions_endtime、versions_xid、versions_operation 是偽列，與 ROWNUM、LEVEL 等其他熟悉的偽列相類似。其他偽列 — 如 VERSIONS_STARTSCN 和 VERSIONS_ENDSCN — 顯示了該時刻的系統更改號。列 versions_xid 顯示了更改該行的事務識別符號。有關該事務的更多詳細資訊可在檢視 FLASHBACK_TRANSACTION_QUERY 中找到，其中列 XID 顯示事務 id。例如，使用上述的 VERSIONS_XID 值 000A000D00000029，UNDO_SQL 值顯示了實際的語句。

注意：

在oracle 11g中如果要看到含連線行的閃回事務查詢操作的組成部分，

或者如果使用叢集表，側必需在使用閃回事務查詢前開啟資料庫的補充日誌：

alter database add supplemental log data;

SELECT UNDO_SQL
FROM FLASHBACK_TRANSACTION_QUERY
WHERE XID = '000A000D00000029';

UNDO_SQL
----------------------------------------------------------------------------
insert into "ANANDA"."RATES"("CURRENCY","RATE") values ('EURO','1.1013');

除了實際語句之外，該檢視還顯示提交操作的時間標記和 SCN、查詢開始時的 SCN 和時間標記以及其他資訊。 

找出一段時期中的變更 

現在，讓我們來看如何有效地使用這些資訊。假設我們需要找出下午 3:57:54 時 RATE 列的值。我們可以執行： 

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

此查詢與閃回查詢類似。在以上的示例中，開始和結束時間為空，表示匯率在該時間段中沒有更改，而是包含一個時間段。還可以使用 SCN 來找出過去的版本值。可以從偽列 VERSIONS_STARTSCN 和 VERSIONS_ENDSCN 中獲得 SCN 號。以下是一個示例： 

select rate, versions_starttime, versions_endtime
from rates versions
between scn 1000 and 1001
/ 

使用關鍵詞 MINVALUE 和 MAXVALUE，可以顯示還原段中提供的所有變更。您甚至可以提供一個特定的日期或 SCN 值作為範圍的一個端點，而另一個端點是文字 MAXVALUE 或 MINVALUE。例如，以下查詢提供那些只從下午 3:57:52 開始的變更，而不是全部範圍的變更： 

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

最終的分析 

閃回版本查詢隨取隨用地複製表變更的短期易變數值審計。這一優點使得 DBA 能夠獲得過去時間段中的所有變更而不是特定值，只要還原段中提供資料，就可以盡情使用。因此，最大的可用版本依賴於 UNDO_RETENTION 引數。 

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