Pavan DBA's Blog

The DBA Knowledge Store

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 🙂

Advertisements

16 Responses to “TRUNCATE not releasing space from the table”

  1. Mussawvir said

    very nice. keep sharing

  2. ravi said

    shrink table is not on all versions rite… re-org better method…shrink is only on automatic space management tablespaces only……

  3. ravi said

    hii pavan,

    Table re-org is also one of the method …and also same time rebuild the index on same table …this is very simple on 8i,9i,10g,11g …..but no access on that table…..

  4. Praveen said

    Hi Pavan,

    Truncate wise it’s a nice information, but why can’t we drop user cascade if client no longer wants the schema and space to be used for other schema ?

    Regards,
    Praveen

  5. Suman said

    Its very nice….tq

  6. Parvinder said

    Good information sir..

  7. jagadeesh said

    Nice explanation sir

  8. thanks for the information… even i had a doubt abt this…

  9. Dinesh said

    Hi Pavan Sir thanks for the info. Here our DBA used to do tablespace reorganization for this type of scenario. What actually they do? Also can you help get some details on HWM and its effect with a truncate.

    Thanks
    Dinesh

  10. Good information bro

  11. Nagaraj said

    Nice, Thanks for sharing information.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: