back to the basics: truncate table reuse storage vs drop storage

From time to time I get questions on my trainings, what is the difference between TRUNCATE TABLE and TRUNCATE TABLE DROP STORAGE… well, there is no difference because DROP STORAGE is default 😉

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. 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 setting, which is the default, is useful for small and medium-sized objects. The extent management in locally managed tablespace is very fast in these cases, so there is no need to reserve space.

But the most confusing part for most of the young DBAs and developers is the "DROP/REUSE STORAGE" clause itself – you have to understand, that truncate does not remove anything from the data files. Never. In both cases. The difference is how other objects behave in that tablespace. REUSE STORAGE means: "this is my private space and only I can reuse it".

As you can see, the second table started consuming the blocks, which belonged to the table before truncate. So part of the data from the first iteration is still there, but the blocks are marked as reusable for other objects in the same tablespace.

If I’ll insert some data to the first table, the new iteration with new DATA_OBJECT_ID will be also consuming those blocks:

So after regular TRUNCATE TABLE (which is in fact: TRUNCATE TABLE DROP STORAGE), the data is not removed from the data files, but the blocks are simple marked to be reused by any segment in the same tablespace.

Now checkout the visualisation, when I repeat all above steps with only little alteration: I’ll use TRUNCATE TABLE RESUSE STORAGE instead of TRUNCATE TABLE:

Now we can see, that the second table is not allowed to consume truncated blocks of the first table. Those blocks are marked for reuse, but only for the original owner.

So to sum up: in both cases, truncate does not remove anything from the datafile. It only marks blocks as reusable – for everyone or for the original owner.