Oracle快速清除表資料，Oracle清除表資料

Oracle快速清除表資料，Oracle清除表資料
Oracle快速清除表資料
一：簡介

 

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

      

二：truncate delete drop

 

        三者都用可以用於清除表資料、其相似與不同之處如下

相同點：

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

        2.drop、truncate都是DDL語句(資料定義語言 (Data Definition Language)),執行後會自動認可。

不同點：

        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;

四：重設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;

調用方式：

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;