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 🙂
Pavan DBA's Blog 
Mussawvir said
very nice. keep sharing
ravi said
shrink table is not on all versions rite… re-org better method…shrink is only on automatic space management tablespaces only……
Pavan DBA said
right now the market is in 11g, so i posted according to that. if someone uses still old versions, then ofcourse they cannot apply.
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…..
Pavan DBA said
table re-org is only when you delete some rows. here it is full delete i.e truncate.
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
Pavan DBA said
yes if client doesn’t want schema, then we can drop it. but in my case client still wants first schema.
Suman said
Its very nice….tq
Parvinder said
Good information sir..
jagadeesh said
Nice explanation sir
prashanth kumar said
thanks for the information… even i had a doubt abt this…
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
Pavan DBA said
there are different methods for doing tablespace re-organization.
1.expdp/impdp
2.move table
3.shrink table
u need to check with ur dba which method they are following from above.
for HWM, see this link – http://asktom.oracle.com/pls/apex/f?p=100:11:0%3A%3A%3A%3AP11_QUESTION_ID:492636200346818072
nagarjunadba said
Good information bro
Nagaraj said
Nice, Thanks for sharing information.
Pavan DBA said
you are welcome 🙂