在Oracle中如何區(qū)別delete，truncate和drop

aixuexiwoying

delete，truncate和drop的區(qū)別

1、truncate和delete只刪除數(shù)據(jù)不刪除表的結(jié)構(gòu)(定義)，而drop語句將刪除表的結(jié)構(gòu)被依賴的約束(constrain)，觸發(fā)器(trigger)，索引(index); 依賴于該表的存儲過程/函數(shù)將保留，但是變?yōu)閕nvalid狀態(tài)。

2、delete語句是dml，這個操作會放到rollback segement中，事務(wù)提交之后才生效;如果有相應(yīng)的trigger，執(zhí)行的時候?qū)⒈挥|發(fā)。

3、truncate，drop是ddl，操作立即生效，原數(shù)據(jù)不放到rollback segment中，不能回滾，操作不觸發(fā)trigger。

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

5、語句執(zhí)行速度，一般來說: drop> truncate > delete

6、小心使用drop 和truncate，尤其沒有備份的時候，想刪除部分數(shù)據(jù)行用delete，注意帶上where子句。

7、想保留表而將所有數(shù)據(jù)刪除，如果和事務(wù)無關(guān)，用truncate即可，如果和事務(wù)有關(guān)，或者想觸發(fā)trigger，還是用delete。

8、如果是整理表內(nèi)部的碎片，可以用truncate跟上reuse stroage，再重新導(dǎo)入/插入數(shù)據(jù)。

9、對于由FOREIGN KEY 約束引用的表，不能使用TRUNCATE TABLE，而應(yīng)使用不WHERE子句的DELETE語句。

       另外，如果沒有備份，drop，delete，truncate是否能夠恢復(fù)？

      從直觀的感覺來說，似乎這個問題沒什么含量，既然都已經(jīng)drop，或者truncate了，是一個ddl語句，數(shù)據(jù)應(yīng)該是回不來了，如果是delete，并且做了commit，數(shù)據(jù)也應(yīng)該回不來了。

      對于這個問題，至少從Oracle的角度來說，答案是可能，而且某些情況下是很可能。我們來一個一個分析：

      首先是drop操作，在Oracle里面，默認情況下是有回收站的功能，就是把一個表做了drop操作之后，其實從物理上這個表并沒有刪除，而是簡單換了一個很長的名字
如果回收站是開啟的（默認開啟）

SQL> show parameter recyclebin

NAME       TYPE    VALUE
---------- ------- ------
recyclebin string  on

       那么這個時候drop的表是否一定能夠恢復(fù)呢，還是不一定，為什么，主要有幾個原因可以考慮：

      比如這個表所在的表空間資源緊張，很可能回收站里的這個表的數(shù)據(jù)就會被回收后存放其它的數(shù)據(jù)了； 或者說某個用戶的配額（quota)本來就不足，這個時候也很可能使用回收站的資源就會受到限制； 或者說在某些場景下，你創(chuàng)建的表直接放在了system表空間下，這個時候哪怕空間充足，也是不能直接恢復(fù)回來的。
   
       所以通過這些分析來看，drop操作還是有很多的可能，但是還是最開始的聲明，是可能而不是肯定。


       再來看看truncate操作，這個操作就算從數(shù)據(jù)恢復(fù)的角度來說，也是無能為力了。但是我們的答案還是可能，這個時候還是要說說flashback database這個特性了。

       比如某個時間點truncate了一個很重要的表。我們可以在情況允許的情況下嘗試flashback database，當然這個特性你也還是有一個系統(tǒng)級的設(shè)置，默認情況下，flashback database的特性是沒有啟用的

SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO

      需要在數(shù)據(jù)庫mount階段使用alter database flashback on來啟用，因為閃回日志記錄數(shù)據(jù)庫級的一些數(shù)據(jù)變化，所以勢必會對數(shù)據(jù)庫的性能和資源造成一定的影響。不過在測試環(huán)境，非正式環(huán)境還是可以大膽使用的。

      比如我們也不太確定truncate的時間，我們可以不斷的閃回，直到找到滿意的結(jié)果未知， 如果在某一個時間點我們發(fā)現(xiàn)數(shù)據(jù)是我們所期望的，我們就可以使用exp把數(shù)據(jù)給導(dǎo)出來，然后再需要的時候把數(shù)據(jù)給導(dǎo)進去。

      最后說說delete操作，這個從Oracle層面來說，選項就更多了。

      delete操作會利用undo的資源，生成相應(yīng)的undo_sql，如果我們delete了數(shù)據(jù)之后做了commit，此時我們可以考慮flashback table這個特性：
flashback table test to timestamp to_timestamp('2015-08-01:12:02:27'，'yyyy-mm-dd:hh24:mi:ss');

       我們也可以把那個時間點的數(shù)據(jù)都查出來：
select * from test as of timestamp  to_timestamp('2015-08-01:14:09:37'，'yyyy-mm-dd:hh24:mi:ss');

       然后再創(chuàng)建一個表，把查詢出來的數(shù)據(jù)導(dǎo)入進去，這樣數(shù)據(jù)就恢復(fù)啦。

更多精彩內(nèi)容：