Oracle快速清除表數據

pooi 9年前發布 | 71K 次閱讀 Oracle 數據庫服務器

一：簡介

刪除表數據一般常用的有三種命令、truncate、delete、drop、但是他們的執行方式、效率和結果各有不同、比如刪除500W記錄級別的數據時、三者之間有很大的不同。

二：truncate delete drop

三者都用可以用于清除表數據、其相似與不同之處如下

相同點：

1.truncate和不帶where子句的delete、以及drop都會刪除表內的數據。

2.drop、truncate都是DDL語句(數據定義語言),執行后會自動提交。

不同點：

1. truncate 和delete 只刪除數據不刪除表的結構(定義) drop 語句將刪除表的結構被依賴的約束(constrain)、觸發器(trigger)、索引(index)；依賴于該表的存儲過程/函數將保留,但是變為 invalid 狀態。

2. delete 語句是數據庫操作語言(dml)，這個操作會放到rollback segment 中，事務提交之后才生效；如果有相應的 trigger，執行的時候將被觸發。truncate、drop 是數據庫定義語言(ddl)，操作立即生效，原數據不放到rollback segment 中，不能回滾，操作不觸發 trigger。

3.delete 語句不影響表所占用的extent，高水線(high watermark)保持原位置不動、drop 語句將表所占用的空間全部釋放。truncate 語句缺省情況下將空間釋放到 minextents個extent，除非使用reuse storage；truncate 會將高水線復位(回到最開始)。

4.速度，一般來說: drop>truncate > delete

5.安全性：小心使用 drop和 truncate，尤其沒有備份的時候.否則哭都來不及、使用上,想刪除部分數據行用 delete，注意帶上where子句. 回滾段要足夠大.想刪除表,當然用 drop、想保留表而將所有數據刪除，如果和事務無關，用truncate即可。如果和事務有關,或者想觸發trigger,還是用delete。如果是整理表內部的碎片，可以用truncate跟上reuse stroage，再重新導入/插入數據。

6.delete是DML語句,不會自動提交。drop/truncate都是DDL語句,執行后會自動提交。

7、TRUNCATE TABLE 在功能上與不帶 WHERE 子句的 DELETE 語句相同：二者均刪除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系統和事務日志資源少。DELETE 語句每次刪除一行，并在事務日志中為所刪除的每行記錄一項。TRUNCATE TABLE 通過釋放存儲表數據所用的數據頁來刪除數據，并且只在事務日志中記錄頁的釋放。

8、TRUNCATE TABLE 刪除表中的所有行，但表結構及其列、約束、索引等保持不變。新行標識所用的計數值重置為該列的種子。如果想保留標識計數值，請改用 DELETE。如果要刪除表定義及其數據，請使用 DROP TABLE 語句。

9、對于有 FOREIGN KEY 約束引用的表，不能使用 TRUNCATE TABLE，而應使用不帶 WHERE 子句的 DELETE 語句。由于 TRUNCATE TABLE 不記錄在日志中，所以它不能激活觸發器。
10、TRUNCATE TABLE 不能用于參與了索引視圖的表。

三：oracle外鍵操作

1．查看所有表的外鍵：

    select table_name, constraint_name from user_constraints where constraint_type = 'R';

2. 禁用所有外鍵約束：

    select 'alter table ' || table_name || ' dsable constraint ' || constraint_name ||';' from user_constraints where constraint_type = 'R';

生成如下sql語句（表名視具體情況而定）：

    alter table RS_ZGSHBX disable constraint FK_RS_ZGSHB_REFERENCE_RY_JBXX;  
    alter table WS_JKQK disable constraint FK_WS_JKQK_REFERENCE_RY_JBXX;  
    alter table WS_YBJZQK disable constraint FK_WS_YBJZQ_REFERENCE_RY_JBXX;

3. 啟用所有外鍵約束：

    select 'alter table ' || table_name || ' enable constraint ' || constraint_name ||';' from user_constraints where constraint_type = 'R';

生成如下sql語句（表名視具體情況而定）：

alter table RS_ZGSHBX enable constraint FK_RS_ZGSHB_REFERENCE_RY_JBXX;  
alter table WS_JKQK enable constraint FK_WS_JKQK_REFERENCE_RY_JBXX;  
alter table WS_YBJZQK enable constraint FK_WS_YBJZQ_REFERENCE_RY_JBXX;

4. 刪除所有外鍵約束：

    select 'alter table ' || table_name || ' drop constraint '|| constraint_name || ';' from user_constraints where constraint_type = 'R';

生成如下sql語句（表名視具體情況而定）：

    alter table RS_ZGSHBX drop constraint FK_RS_ZGSHB_REFERENCE_RY_JBXX;  
    alter table WS_JKQK drop constraint FK_WS_JKQK_REFERENCE_RY_JBXX;  
    alter table WS_YBJZQK drop constraint FK_WS_YBJZQ_REFERENCE_RY_JBXX;

</div> </div>

四：重置sequence初始值

Oracle中、主鍵常常使用的是指定的sequence來實現自動增長、本身沒有什么問題、但是當我們使用truncate清除千萬級別的數據之后、sequence每次增長1的話、刪除后下次再插入數據時、其id已經是千萬以上了、這一般不是我們想要的、Oracle中一般將自增 sequence重置為初始1時，都是刪除再重建，這種方式有很多弊端，依賴它的函數和存儲過程將失效，需要重新編譯。不過還有種巧妙的方式，不用刪除，利用步長參數，先查出sequence的nextval，記住，把遞增改為負的這個值（反過來走），然后再改回來。

假設需要修改的序列名：seq_name：

    create or replace procedureseq_reset(v_seqname varchar2) as n number(10);  
           tsqlvarchar2(100);  
    begin  
           execute immediate'select '||v_seqname||'.nextval from dual' into n;  
            n:=-(n-1);  
            tsql:='altersequence '||v_seqname||' increment by '|| n;  
            execute immediatetsql;  
            execute immediate'select '||v_seqname||'.nextval from dual' into n;  
            tsql:='altersequence '||v_seqname||' increment by 1';  
            execute immediatetsql;  
    end seq_reset;

</div> </div>

調用方式：

    declare  
           seq_namevarchar2(100);  
     begin  
           seq_name :='seq_ry_jbxx';  
          seq_reset(seq_name);  
     end;

五：有外鍵約束的情況下使用truncate

如前面提到過、有FOREIGNKEY（外鍵）約束的表不能使用TRUNCATE命令、但是可以通過禁用表的外鍵、然后使用TRUNCATE 清除表數據、最后再恢復表外鍵的方式來達到目的。

1、生成禁用外鍵的語句、并導出sql。

2、生成啟用外鍵約束語句、并導出sql。

3、重新整理成新的sql文件。

4、在上面新的sql文件中加入對RY_JBXX表對應的sequence:seq_ry_jbxx進行重置。

5、完整sql文件內容（其中seq_reset是四中生成的存儲過程、這里直接調用）：

alter table RS_ZGSHBX disable constraintFK_RS_ZGSHB_REFERENCE_RY_JBXX;

alter table WS_JKQK disableconstraint FK_WS_JKQK_REFERENCE_RY_JBXX;

alter table WS_YBJZQK disableconstraint FK_WS_YBJZQ_REFERENCE_RY_JBXX;
truncate tableRY_JBXX;

alter table RS_ZGSHBX enable constraintFK_RS_ZGSHB_REFERENCE_RY_JBXX;

alter table WS_JKQK enableconstraint FK_WS_JKQK_REFERENCE_RY_JBXX;

alter table WS_YBJZQK enableconstraint FK_WS_YBJZQ_REFERENCE_RY_JBXX;
select count(*) from ry_jbxx;

 declare

       seq_name varchar2(100);

 begin

       seq_name :='seq_ry_jbxx';

       seq_reset(seq_name);

 end;  </pre></div>
</div>
</div>
轉自：http://blog.csdn.net/crave_shy/article/details/42291965

本文由用戶 pooi 自行上傳分享，僅供網友學習交流。所有權歸原作者，若您的權利被侵害，請聯系管理員。

轉載本站原創文章，請注明出處，并保留原始鏈接、圖片水印。

本站是一個以用戶分享為主的開源技術平臺，歡迎各類分享！

本文地址：http://www.baiduhome.net/lib/view/open1420010697531.html

Oracle 數據庫服務器

Oracle快速清除表數據

一：簡介

二：truncate delete drop

三：oracle外鍵操作

四：重置sequence初始值

五：有外鍵約束的情況下使用truncate

相關經驗

相關資訊

相關文檔

目錄