When we delete the rows from the table using the DELETE command, the extents that are allocated to the table will not be released and the table still holds them, whereas when using TRUNCATE with DROP STORAGE clause (which is the default), it will release the space back to the tablespace.
But we see cases that event truncating won’t release the space back to the tablespace.
The answer is simple, When a truncate is issued on a table, Oracle deallocates all space used by the removed rows except that specified by the MINEXTENTS storage parameter. However, if the minextent (along with the initial value) is large enough, this space is NOT released even after the truncate.
For example, see a table in my case which is of 586 MB in size
CREATE TABLE XYZ
( COL1 VARCHAR2(45 BYTE),
COL2 VARCHAR2(45 BYTE),
COL3 VARCHAR2(6 BYTE) )
TABLESPACE XYZ_DATA
RESULT_CACHE (MODE DEFAULT)
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 100M
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
From the above, we can see that the initial extent size itself is 100M. If we truncate this table which is having a total size of 586 MB, Oracle will de-allocate only 486 MB and will keep that 100 MB attached to the table.
1. export the table to save the data.
2. drop the table and recreate it with a small INITIAL size.
3. import the data back.