Pavan DBA's Blog

The DBA Knowledge Store

Posts Tagged ‘truncate’

TRUNCATE not releasing space from the table

Posted by Pavan DBA on January 9, 2014


Hello Friends,

this will be my first article in 2014… so before starting with, wishing all my blog readers a VERY HAPPY & PROSPEROUS NEW YEAR 2014.

Today, I want to explain about a typical behaviour of truncate. To start with, let’s look at the basic difference between delete and truncate at storage point of view.

As most of you people already know, when we delete the rows from the table using DELETE command, the extents that are allocated to the table will not be released and still table holds them, where as when using TRUNCATE with DROP STORAGE clause (which is default), it will release the space back to tablespace.

Yesterday, I have got one requirement to truncate all tables in all schemas of a database in order to release the space to tablespace (basically client want to use that space for a new schema). So, I have proceeded and found that even after truncating all tables, i could able to release only 1 GB of space to tablespace whereas my expectation is to get 45GB. Moreover, while truncating, I have used DROP STORAGE clause also.

I tried using move table, shrink space after the truncate, but still nothing productive happened.

This is a bit surprise to me and finally, I realized things as below…

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 initial extent size itself is 100M. Suppose, if we truncate this table which is having total size as 586 MB, Oracle will de-allocate only 486 MB and will keep that 100 MB attached to the table.

Like this if you have more no.of tables, the situation where the space is not releasing to tablespace will arise.

Solutions to deal this…

1. export & import of the tables either using exp/imp or expdp/impdp 2. drop the table and create it again (anyway we planned to issue truncate, so dropping won’t make big difference)

So, beware of TRUNCATE friends… if anyone has a better solution, please leave that in comments.

HAPPY LEARNING ūüôā

Posted in Admin | Tagged: , , , , | 16 Comments »

 
%d bloggers like this: