oracle删除大資料方法

1、使用TRUNCATE指令進行删除。 

如果是整個表的資料都要删除的話，使用TRUNCATE TABLE指令是理想的選擇。它删除了表中的所有資料，并且因為不寫REDO LOG FILE，是以速度很快。删除的同時，表的索引和限制條件仍然存在。這種方法适用于ORACLE的各個版本。但是當要删除的資料隻是表中的一部分時，這種方法便行不通了。 

2、直接進行DELETE操作。 

直接使用DELETE指令進行删除，如果删除的資料量較大時，可能導緻復原段出錯。這是因為在删除資料的過程中，不斷擴充復原段，直到復原段的最大範圍數

或復原段所在表空間空閑空間用完而出錯。解決這個問題可以通過給删除資料的事務指定一個足夠大的復原段或者将復原段所在表空間的AUTOEXTEND選項打開，同時将復原段的MAXEXTENTS改大或設為UNLIMITED。不過這樣仍存在一個隐患，如果删除的資料量大，同時資料庫工作于歸檔模式下時，有可能導緻日志切換頻繁，所有日志檔案都處于需要歸檔的狀況，而歸檔程序來不及歸檔日志檔案的情況出現，這時資料庫将被挂起，直到有可用的日志檔案後才恢複正常。 

是以這種方法也不理想。 

3、通過PL/SQL循環分段删除。 

第三種方法是專門針對上面第二種方法進行優化改進的。這種方法通過一段PL/SQL程式循環分段删除資料，逐漸送出事務，達到縮小事務規模，安全删除資料的目的。 

例如有一個資料表t_table，我們将對其中字段c_date滿足小于2001年1月1日的記錄進行删除，可以采用以下的PL/SQL程式。 

1 DECLARE 

2 V_TEMP NUMBER; 

3 BEGIN 

4 LOOP 

5 BEGIN 

6 SELECT 1 INTO V_TEMP FROM t_table WHERE c_date < to_date(2000/01/01,yyyy/mm/dd) AND rownum = 1; 

7 DELETE FROM t_table WHERE c_date < to_date(2000/01/01,yyyy/mm/dd) AND rownum < 100; 

8 COMMIT; 

9 EXCEPTION 

10 WHEN NO_DATA_FOUND THEN 

11 EXIT; 

12 END; 

13 END LOOP; 

14 END; 

程式的第1和第2行聲明了一個臨時變量。第4到第13行定義了一個循環，在這個循環中第6行不斷檢查表中是否還有滿足條件的記錄，如果有，第7行程式便執行删除操作，每次删除100記錄，同時送出事務。當表中已無滿足條件的記錄時，便引起NO_DATA_FOUND的異常，進而退出循環。通過分批删除，逐漸送出，縮小了事務的規模，進而達到避免出現復原段錯誤的目的。然而這種方法依然存在因日志切換頻繁，而歸檔程序來不及歸檔日志檔案而導緻資料庫挂起的可能性。下面的程式通過ORACLE所提供的dbms_lock包中的過程sleep，解決了這個問題，進而達到安全快速大量删除資料的目的。

2 V_LOGNUM NUMBER; -- 資料庫中擁有的日志檔案數 

3 V_NEEDARC NUMBER; -- 需要歸檔的日志檔案數 

4 BEGIN 

5 SELECT count(1) INTO V_LOGNUM FROM V$LOG; 

6 LOOP 

7 LOOP 

8 SELECT count(1) INTO V_NEEDARC FROM V$ARCHIVE; 

9 IF V_NEEDARC < V_LOGNUM - 1 THEN 

10 EXIT; 

11 ELSE 

12 DBMS_LOCK.SLEEP(60); 

13 END IF; 

14 END LOOP; 

15 

16 DELETE FROM t_table WHERE c_date < to_date(2000/01/01,yyyy/mm/dd) AND rownum < 100; 

17 IF SQL%ROWCOUNT = 0 THEN 

18 EXIT; 

19 END IF; 

20 COMMIT; 

21 END LOOP; 

22 END; 

程式中的第2和第3行聲明了兩個變量v_lognum和v_needarc來儲存資料庫中日志檔案的數量和目前需要歸檔的日志檔案數量。 

第5行擷取了資料庫中日志檔案的數量。 

第6行到第21行開始了删除資料的循環，第7行到第14行是一個子循環，不斷檢測目前需要歸檔的日志檔案的數量v_needarc是否小于資料庫的日志檔案總數v_lognum減去1，如果滿足條件，則退出子循環，開始删除資料。否則的話便調用dbms_lock.sleep()過程，使程式休眠60秒，然後繼續子循環，檢測需歸檔的日志檔案數量。 

第17到19行，檢查删除資料的結果，如果已無資料，則退出，程式結束。 

這個程式，通過利用dbms_output.sleep()過程，在删除過程中當需要歸檔的日志檔案達到認定的限制時，使删除過程暫時停止，等待ARCH程序将日志檔案歸檔後再繼續進行，進而達到避免歸檔日志檔案來不及歸檔，導緻資料庫挂起的問題。 

此方法适用于oracle的各個版本。 

4、使用NOLOGGING選項重建立表。 

在ORACLE 8以後的版本中，CREATE TABLE指令提供了NOLOGGING的選項，在建表時不用寫日志檔案。 

這樣當我們在删除大量的資料時可以将要保留的資料通過CREATE TABLE ... NOLOGGING ... AS SELECT * FROM...的方法将要保留的資料備份到另一個表中，将原來的表删除，然後再 ALTER TABLE RENAME TO 指令将備份的表改為原來表的名字。 

這個方法由于不寫日志檔案，是以速度很快，但是原來的表所擁有的索引和限制都将不存在，需重建立立。另外這個方法隻适用于ORACLE 8以後的版本，ORACLE7.3中也可采用這個方法，但NOLOGGIN關鍵字要由UNRECOVERABLE代替。 

前面比較了在ORACLE中如何批量删除資料的幾種方法，以上這幾種方法均在oracle 8i for windows，oracle 7.3 for windows，oracle 7.1 for netware中測試通過，在應用中可以根據實際情況選擇一種合适的方法進行處理。