The Geek Diary

Truncate Table Statement: REUSE STORAGE VS DROP STORAGE

by

Question: If we want to truncate any large table and reclaim space, which is the truncate command out of below 3 which can be used?

TRUNCATE TABLE table_name;

OR

TRUNCATE TABLE table_name DROP STORAGE;

OR

TRUNCATE TABLE table_name REUSE STORAGE;

REUSE STORAGE VS DROP STORAGE

Here is the difference between drop and reuse storage:

DROP STORAGE

Specify DROP STORAGE to deallocate all space from the deleted rows from the table except the space allocated by the MINEXTENTS parameter of the table or cluster. This space can subsequently be used by other objects in the tablespace. Oracle Database also sets the NEXT storage parameter to the size of the last extent removed from the segment in the truncation process. This is the default.

REUSE STORAGE

Specify REUSE STORAGE to retain the space from the deleted rows allocated to the table. Storage values are not reset to the values when the table or cluster was created. This space can subsequently be used only by new data in the table or cluster resulting from insert or update operations. This clause leaves storage parameters at their current settings.

So, if you want to release the space to tablespace you can use drop else you can use reuse.

You May Also Like