oracle存儲過程truncate

『壹』 truncate和delete之間有什麼區別

truncate和delete的主要區別：

1、delete是DML，執行delete操作時，每次從表中刪除一行，並且同時將該行的的刪除操作記錄在redo和undo表空間中以便進行回滾（rollback）和重做操作，但要注意表空間要足夠大，需要手動提交（commit）操作才能生效，可以通過rollback撤消操作。

2、delete可根據條件刪除表中滿足條件的數據，如果不指定where子句，那麼刪除表中所有記錄。

3、delete語句不影響表所佔用的extent，高水線(high watermark)保持原位置不變。

4、truncate是DDL，會隱式提交，所以，不能回滾，不會觸發觸發器。

5、truncate會刪除表中所有記錄，並且將重新設置高水線和所有的索引，預設情況下將空間釋放到minextents個extent，除非使用reuse storage，。不會記錄日誌，所以執行速度很快，但不能通過rollback撤消操作（如果一不小心把一個表truncate掉，也是可以恢復的，只是不能通過rollback來恢復）。

6、對於外鍵（foreignkey ）約束引用的表，不能使用 truncate table，而應使用不帶 where 子句的 delete 語句。

7、truncatetable不能用於參與了索引視圖的表。

(1)oracle存儲過程truncate擴展閱讀：

在速度上，一般來說truncate>delete。

如果想保留表而將所有數據刪除，如果和事務無關，用truncate就好。

如果和事務有關，或者想觸發trigger，還是用delete。

『貳』 Oracle 刪除表中記錄 如何釋放表及表空間大小

解決方案

執行

sql">altertablejk_testmove

或

altertablejk_testmovestorage(initial64k)

或

altertablejk_testdeallocateunused

或

altertablejk_testshrinkspace.

注意：因為alter table jk_test move 是通過消除行遷移，清除空間碎片，刪除空閑空間，實現縮小所佔的空間，但會導致此表上的索引無效（因為ROWID變了，無法找到），所以執行 move 就需要重建索引。

找到表對應的索引

selectindex_name,table_name,tablespace_name,index_type,statusfromdba_indexeswheretable_owner='SCOTT'

根據status 的值，重建無效的就行了。sql='alter index '||index_name||' rebuild'; 使用存儲過程執行，稍微安慰。

還要注意alter table move過程中會產生鎖，應該避免在業務高峰期操作！

另外說明：truncate table jk_test 會執行的更快，而且其所佔的空間也會釋放，應該是truncate 語句執行後是不會進入oracle回收站（recylebin）的緣故。如果drop 一個表加上purge 也不會進回收站(在此裡面的數據可以通過flashback找回)。

不管是delete還是truncate 相應數據文件的大小並不會改變，如果想改變數據文件所佔空間大小可執行如下語句：

alterdatabasedatafile'filename'resize8g

重定義數據文件的大小（不能小於該數據文件已用空間的大小）。

另補充一些PURGE知識

Purge操作：

1). Purge tablespace tablespace_name : 用於清空表空間的Recycle Bin

2). Purge tablespace tablespace_name user user_name: 清空指定表空間的Recycle Bin中指定用戶的對象

3). Purge recyclebin: 刪除當前用戶的Recycle Bin中的對象。

4). Purge dba_recyclebin: 刪除所有用戶的Recycle Bin中的對象，該命令要sysdba許可權

5). Drop table table_name purge:刪除對象並且不放在Recycle Bin中，即永久的刪除，不能用Flashback恢復。

6). Purge index recycle_bin_object_name： 當想釋放Recycle bin的空間，又想能恢復表時，可以通過釋放該對象的index所佔用的空間來緩解空間壓力。 因為索引是可以重建的。

二、如果某些表佔用了數據文件的最後一些塊，則需要先將該表導出或移動到其他的表空間中，然後刪除表，再進行收縮。不過如果是移動到其他的表空間，需要重建其索引。

1、

SQL>altertablet_objmovetablespacet_tbs1;---移動表到其它表空間

也可以直接使用exp和imp來進行

2、

SQL>alterowner.index_namerebuild;--重建索引

3、刪除原來的表空間

『叄』 Oracle資料庫的面試題目及答案

Oracle資料庫的面試題目及答案

基礎題目：

1. 比較truncate和 命令

解答：兩者都可以用來刪除表中所有的記錄。區別在於：truncate是DDL操作，它移動HWK，不需要 rollback segment .

而Delete是DML操作, 需要rollback segment 且花費較長時間.

【相同點

truncate和不帶where子句的, 以及drop都會刪除表內的數據

不同點:

1. truncate和 只姿轎刪除數據不刪除表的結構(定跡談肆義)

drop語句將刪除表的結構被依賴的約束(constrain),觸發器(trigger),索引(index); 依賴於該表的.存儲過程/函數將保留,

但是變為invalid狀態.

2.語句是dml,這個操作會放到rollback segement中,事務提交之後才生效;如果有相應的trigger,執行的時候將被觸發.

truncate,drop是ddl, 操作立即生效,原數據不放到rollback segment中,不能回滾. 操作不觸發trigger.

3.語句不影響表所佔用的extent, 高水線(high watermark)保持原位置不動

顯然drop語句將表所佔用的空間全部釋放

truncate 語句預設情況下見空間釋放到 minextents個 extent,除非使侍渣用reuse storage; truncate會將高水線復位(回到最開始).

4.速度,一般來說: drop>; truncate >;

5.安全性:小心使用drop 和truncate,尤其沒有備份的時候.否則哭都來不及

使用上,想刪除部分數據行用,注意帶上where子句. 回滾段要足夠大.

想刪除表,當然用drop

想保留表而將所有數據刪除. 如果和事務無關,用truncate即可. 如果和事務有關,或者想觸發trigger,還是用.

如果是整理表內部的碎片,可以用truncate跟上reuse stroage,再重新導入/插入數據

】

2.Oracle中，需要在查詢語句中把空值(NULL)輸出為0，如何處理?

答案：nvl(欄位，0).

nvl( ) 函數

從兩個表達式返回一個非 null 值。

語法

NVL(eExpression1, eExpression2)

參數

eExpression1, eExpression2

如果 eExpression1 的計算結果為 null 值，則 NVL( ) 返回 eExpression2。如果 eExpression1 的計算結果不是 null 值，

則返回 eExpression1。eExpression1 和 eExpression2 可以是任意一種數據類型。如果 eExpression1 與 eExpression2

的結果皆為 null 值，則 NVL( ) 返回 .NULL.。

返回值類型

字元型、日期型、日期時間型、數值型、貨幣型、邏輯型或 null 值

說明

在不支持 null 值或 null 值無關緊要的情況下，可以使用 NVL( ) 來移去計算或操作中的 null 值。

select nvl(a.name,空得) as name from student a join school b on a.ID=b.ID

注意：兩個參數得類型要匹配

3.Oracle中char和varchar2數據類型有什麼區別?有數據」test」分別存放到10)和varchar2(10)類型的欄位中，

其存儲長度及類型有何區別?

答案：

區別： 1).CHAR的長度是固定的，而VARCHAR2的長度是可以變化的， 比如，存儲字元串「test"，對於CHAR (10)，