-- 建立表空間與用戶
CREATE TABLESPACE zwc DATAFILE 'C:\oracle\zwc01.dbf' SIZE 500M;
CREATE USER zwc IDENTIFIED BY zwc;
GRANT RESOURCE, CONNECT TO zwc;
-- 建立測試表並插入 200 萬筆資料
CONN zwc/zwc
CREATE TABLE zwc.tab01 (
a INT PRIMARY KEY,
b VARCHAR2(100),
c VARCHAR2(100),
d DATE DEFAULT SYSDATE
) TABLESPACE zwc;
CREATE OR REPLACE PROCEDURE p_inst_tab01 AS
BEGIN
FOR i IN 1..2000000 LOOP
INSERT INTO tab01(a,b,c,d) VALUES (i,i,i,SYSDATE);
IF MOD(i,2000)=0 THEN COMMIT; END IF;
END LOOP;
END;
/
EXEC p_inst_tab01; -- 耗時約數分鐘
CONN / AS SYSDBA
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA; -- 必須開啟
-- 模擬誤刪除 100 筆、誤更新 10 筆
DELETE FROM zwc.tab01 WHERE rownum <= 100;
UPDATE zwc.tab01 SET d = SYSDATE-100 WHERE rownum <= 10;
COMMIT;
-- 設定 UTL_FILE_DIR 需重啟實例生效
ALTER SYSTEM SET utl_file_dir='c:\oracle' SCOPE=SPFILE;
SHUTDOWN IMMEDIATE; STARTUP;
-- 建立 LogMiner 字典檔
EXEC DBMS_LOGMNR_D.BUILD(
dictionary_filename => 'test.ora',
dictionary_location => 'c:\oracle'
);
-- 掛載歸檔日誌(需指定正確路徑)
EXEC DBMS_LOGMNR.ADD_LOGFILE(
logfilename => 'C:\arch\ARC00041_0842045960.001',
options => DBMS_LOGMNR.NEW
);
-- 啟動 LogMiner
EXEC DBMS_LOGMNR.START_LOGMNR(dictFilename => 'c:\oracle\test.ora');
-- 將挖掘結果存入表
CREATE TABLE zwc.t_logminer TABLESPACE zwc AS
SELECT * FROM v$logmnr_contents;
EXEC DBMS_LOGMNR.END_LOGMNR;
-- 查看挖掘筆數
SELECT COUNT(*) FROM zwc.t_logminer; -- 212
SELECT COUNT(*) FROM zwc.t_logminer
WHERE seg_name='TAB01' AND seg_owner='ZWC'; -- 110
-- 取得誤刪除的回滾 SQL (SQL_UNDO)
SELECT sql_undo
FROM t_logminer
WHERE seg_name='TAB01' AND seg_owner='ZWC' AND operation='DELETE';
輸出範例:
INSERT INTO "ZWC"."TAB01"("A","B","C","D")
VALUES ('201','201','201',TO_DATE('02-12月-13','DD-MON-RR'));
...
| 欄位 | 用途 |
|---|---|
| SQL_UNDO | 回滾 SQL,用於恢復誤刪除/誤更新前的資料 |
| SQL_REDO | 重做 SQL,即原本執行的誤操作 |
| OPERATION | 操作類型:INSERT/UPDATE/DELETE/DDL |
| SEG_OWNER, SEG_NAME | 段擁有者與段名稱 |
| TIMESTAMP, SCN | 操作時間與系統變更號 |
SUPPLEMENTAL LOG DATA (ALL) 以捕獲完整欄位值。SQL_UNDO,避免單一交易過大。FLASHBACK TABLE、FLASHBACK QUERY 可處理更近期的誤操作(需啟用 UNDO 保留與閃回區)。