oracle 9i 閃回

今天一個同事錯誤刪除了一些記錄，已經有15分鐘了<wbr>，我們只能嘗試著使用9i的閃回功能，看看能否將記錄"拿回來"：

首先將刪除的記錄存放在一個臨時表中

create table tm_gaogao as select * from tf_r_simcard as of timestamp(systimestamp - interval'1200' second);

然後挑出錯誤刪除的記錄，插回原表

insert into tf_r_simcard select * from tm_gaogao where simcardid like '5555%' ;

搞定。

以下是oracle文件中其他閃回sql:

INSERT INTO employee
(SELECT * FROM employee AS OF
TIMESTAMP (SYSTIMESTAMP - INTERVAL '60' MINUTE))
MINUS SELECT * FROM employee);
CREATE VIEW hour_ago AS
SELECT * FROM employee AS OF
TIMESTAMP (SYSTIMESTAMP - INTERVAL '60' MINUTE);
INSERT INTO employee
(SELECT * FROM employee AS OF TIMESTAMP
TO_TIMESTAMP('2003-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS')
WHERE name = 'JOHN');

這是我使用閃回功能恢復的時間最長的資料，寫在這裡做個紀念。

[@more@]