關於delete,drop,truncate的問題

有一個很常規的問題大量出現在筆試面試中，就是delete,truncate和drop的區別，當然這個問題我們也可以昇華一下，透過這個簡單的問題其實可以關聯到Oracle的一些特性。
我們先來看看常規的問題，常規的回答。
從網上也搜了一些答案，自己也略微做了改動。
相同點:

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

2.drop，truncate都是DDL(資料定義語言)語句，執行後會自動提交。
不同點：

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

2.delete語句是dml,這個操作會放到rollback segement中,事務提交之後才生效;如果有相應的trigger,執行的時候將被觸發.
truncate,drop是ddl, 操作立即生效,原資料不放到rollback segment中,不能回滾. 操作不觸發trigger.
3.delete語句不影響表所佔用的extent, 高水線(high watermark)保持原位置不動
   drop語句將表所佔用的空間全部釋放
   truncate 語句預設情況下將資料空間釋放,除非使用reuse storage; truncate會將高水線復位(回到最開始).
4.速度,一般來說: drop> truncate > delete

5.安全性:

小心使用drop 和truncate,尤其沒有備份的時候.使用上,想刪除部分資料行用delete,注意帶上where子句. 回滾段要足夠大.
想刪除表,當然用drop
想保留表而將所有資料刪除. 如果和事務無關,用truncate即可. 如果和事務有關,或者想觸發trigger,還是用delete.
如果是整理表內部的碎片,可以用truncate跟上reuse stroage,再重新匯入/插入資料

6.對於由 FOREIGN   KEY 約束引用的表，不能使用 TRUNCATE   TABLE，而應使用不WHERE子句的DELETE語句。

這個問題可能主要的意圖就是能夠簡單做一個區分即可，可能裡面還是有一些細節的知識點一時不會想到。
我們不能止步於此，如果是一個dba面試，我們可以把這個問題昇華一下，

如果沒有備份，drop,delete,truncate是否能夠恢復？
從直觀的感覺來說，似乎這個問題沒什麼含量，既然都已經drop,或者truncate了，是一個ddl語句，資料應該是回不來了，如果是delete，並且做了commit，資料也應該回不來了。
對於這個問題，至少從Oracle的角度來說，答案是可能，而且某些情況下市很可能。我們來一個一個分析
首先是drop操作，在Oracle裡面，預設情況下是有回收站的功能，就是把一個表做了drop操作之後，其實從物理上這個表並沒有刪除，而是簡單換了一個很長的名字，
比如我們做一個簡單的例子。

SQL> create table tt tablespace data as select *from cat;

Table created.

SQL> drop table tt;

Table dropped.

SQL> show recycle   --這個時候去檢視回收站就會看到drop的表起了一個別名，當然這個表我們不能繼續做dml操作了，但是可以簡單查詢裡面的資料。
ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
TT               BIN$HEEa4lcomhDgUKjAgAMeeg==$0 TABLE        2015-08-01:21:42:18
當然回收站的功能在系統級有一個引數配置。預設是開啟的。

SQL> show parameter recyclebin

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
recyclebin                           string      on
所以這個時候我們能說drop的資料一定能夠恢復呢，還是不一定，為什麼，主要有幾個原因可以考慮。
比如這個表所在的表空間資源緊張，很可能回收站裡的這個表的資料就會被回收後存放其它的資料了。
或者說某個使用者的配額（quota)本來就不足，這個時候也很可能使用回收站的資源就會受到限制，
或者說在某些場景下，你建立的表直接放在了system表空間下，這個時候哪怕空間充足，也是不能直接恢復回來的。

所以透過這些分析來看，drop操作還是有很多的可能，但是還是最開始的宣告，是可能而不是肯定。

再來看看truncate操作，這個操作就算從資料恢復的角度來說，也是無能為力了。但是我們的答案還是可能，這個時候還是要說說flashback database這個特性了。
比如某個時間點truncate了一個很重要的表。我們可以在情況允許的情況下嘗試flashback database
 當然這個特性你也還是有一個系統級的設定，預設情況下，flashback database的特性是沒有啟用的，SQL> select flashback_on from v$database;

FLASHBACK_ON
------------------
NO
需要在資料庫Mount階段使用alter database flashback on來啟用，因為閃回日誌記錄資料庫級的一些資料變化，所以勢必會對資料庫的效能和資源造成一定的影響。不過在測試環境，非正式環境還是可以大膽使用的。
比如我們也不太確定truncate的時間，我們可以不斷的閃回，直到找到滿意的結果未知，比如下面的步驟，我們閃回了兩次，給定了兩個時間點。

–Startup mount
–Flashback database to timestamp xxxxx時間1;
–Alter database open read only;
–Shutdown immediate
–Startup mount
–Flashback database to timestamp xxxxx時間2;
–Shutdown immediate
–Startup mount
–Alter database open resetlogs;
比如在某一個時間點我們發現資料是我們所期望的，我們就可以使用exp把資料給匯出來，這就有點類似這些年來的所說的一個熱詞"穿越”，在資料庫級我們級可以實現這樣的穿越。
然後再需要的時候把資料給導進去。
當然了，這是一種預設的體系結構，如果想使用非常規手段，其實也可以做一些工作，因為在truncate的時候，其實資料檔案裡的資料塊還沒有立即清空，只是從資料字典級做了清空，同時把高水位線給降下來了。如果這個時候能夠馬上讀取所在的資料檔案，還是可能把資料給找回來，當然這是非常規方法了。但是也是一種思路。
所以看似不可能的truncate操作恢復也是有可能。

再來說說最後的delete操作，這個從Oracle層面來說，選項就更多了。
首先，delete操作會利用undo的資源，生成相應的Undo_sql,如果我們delete了資料之後做了，commit
其實可以從以下幾個角度來考慮，當然都是閃回的一些特性了，
比如我們可以考慮flashback table這個特性，當然也是有一個開關，比如表名為test，則需要使用alter table test enable row movement來啟用行移動，這個時候delete資料之後還是可能把資料透過閃回表給找回來。
比如我們可以這麼做，
 flashback table test to timestamp to_timestamp('2015-08-01:14:09:37','yyyy-mm-dd:hh24:mi:ss');
當然我們如果沒有啟用行移動，也沒關係，我們可以使用flashback query.
這個特性從某種程度上來說，使用的歡迎程度要遠高於flashback table,因為通用而且實用，
比如我們可以這麼做。
select * from test as of timestamp  to_timestamp('2015-08-01:14:09:37','yyyy-mm-dd:hh24:mi:ss');
這個時候就會把那個時間點的資料都查出來了。還可以靈活調整時間，直到滿意為止，這個時候可以略微改進一下。我們建立一個"臨時表"把資料給持久下來。
create table xxxx select * from test as of timestamp  to_timestamp('2015-08-01:14:09:37','yyyy-mm-dd:hh24:mi:ss');
甚至在一些場景下，我們還可以使用flashback transaction,或者flashback version query來輔助完成一些特殊場景的資料恢復。
所以可以看到，閃回這個特性有不同的實現方式，資料的恢復還是有很多的可能。

從這個問題我們可以看到，有些問題我們略微改進一下，就會使得資料的恢復在一些場景下有一定的可能。