講解Oracle數據庫10g新特性中的閃回查詢

2024-08-29 13:52:44

字體：大中小

來源：轉載

供稿：網友

這篇論壇文章（賽迪網技術社區）深入探討了Oracle數據庫10g新特性中的閃回查詢，更多相關內容請參考下文。

不需要設置，立即識別對行的所有更改

在 Oracle9i Database 中，我們看到它推出了以閃回查詢形式表示的“時間機器”。該特性允許 DBA 看到特定時間的列值，只要在還原段中提供該數據塊此前鏡像的拷貝即可。但是，閃回查詢只提供某時刻數據的固定快照，而不是在兩個時間點之間被更改數據的運行狀態表示。某些應用程序，如涉及到外幣管理的應用程序，可能需要了解一段時期內數值數據的變化，而不僅僅是兩個時間點的數值。由于閃回版本查詢特性，Oracle Database 10g 能夠更方便高效地執行該任務。

查詢對表的更改

在本示例中，我使用了一個銀行外幣管理應用程序。其數據庫含有一個名稱為 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 值顯示了實際的語句。

　　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