實驗步驟
1. 建立測試表 (10,000 筆)
CREATE TABLE flb_test(id NUMBER, dd DATE);
BEGIN
FOR i IN 1..10000 LOOP
INSERT INTO flb_test VALUES (i, SYSDATE+i);
END LOOP;
END;
/
EXEC DBMS_STATS.GATHER_TABLE_STATS('TEST_USER1','FLB_TEST');
2. 記錄基準時間與 SCN
SELECT TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') FROM DUAL; -- 2014-10-13 19:23:29
SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL; -- 1144357
3. 查看表區塊與索引狀態
SELECT segment_name, bytes/1024/1024 size_m, blocks FROM user_segments WHERE segment_name='FLB_TEST';
-- SIZE_M=0.25, BLOCKS=32
CREATE INDEX ind_flb ON flb_test(id,dd);
SELECT index_name, status, blevel, leaf_blocks FROM dba_indexes WHERE index_name='IND_FLB';
-- BLEVEL=1, LEAF_BLOCKS=33
4. 刪除一半資料並提交
DELETE FROM flb_test WHERE id <= 5000;
COMMIT;
EXEC DBMS_STATS.GATHER_TABLE_STATS('TEST_USER1','FLB_TEST');
EXEC DBMS_STATS.GATHER_INDEX_STATS('TEST_USER1','IND_FLB');
5. 啟用 ROW MOVEMENT 並閃回
ALTER TABLE flb_test ENABLE ROW MOVEMENT;
FLASHBACK TABLE flb_test TO TIMESTAMP TO_TIMESTAMP('2014-10-13 19:23:29','YYYY-MM-DD HH24:MI:SS');
-- Flashback complete.
6. 重新收集統計資訊並驗證
EXEC DBMS_STATS.GATHER_TABLE_STATS('TEST_USER1','FLB_TEST');
EXEC DBMS_STATS.GATHER_INDEX_STATS('TEST_USER1','IND_FLB');
SELECT COUNT(*) FROM flb_test; -- 10000 (資料恢復)
SELECT index_name, status, blevel, leaf_blocks FROM dba_indexes WHERE index_name='IND_FLB';
-- BLEVEL=1, LEAF_BLOCKS=33 (索引結構保持)
關鍵發現
- 閃回表僅恢復資料，**索引結構 (BLEVEL, LEAF_BLOCKS) 保持不變**，狀態維持 VALID
- 必須
ENABLE ROW MOVEMENT 才能閃回
- 閃回後 **務必重新收集統計資訊**，否則執行計畫可能不準確
- 可指定 TIMESTAMP 或 SCN 閃回
適用場景
- 誤刪/誤更新資料快速恢復 (秒級)
- 測試環境快速重置基準資料
- 配合
FLASHBACK DATABASE 做更大範圍恢復
