Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Strange Truncate Table Behaviour

RE: Strange Truncate Table Behaviour

From: Kline.Michael <>
Date: Thu, 27 Oct 2005 09:14:52 -0400
Message-ID: <>

Compare the results of dba_segments to dba_extents. I know with partitions on on HP, I had to change my query to use dba_extents as dba_segments may show 2-5GB, but dba_extents showed 1 extent.  

Michael Kline

Database Administration

Outside 804.261.9446

Cell 804.744.1545


-----Original Message-----
[] On Behalf Of Sam Bootsma
Sent: Thursday, October 27, 2005 9:06 AM To:
Subject: Strange Truncate Table Behaviour  

Yesterday I used the Oracle supplied script sptrunc.sql to truncate StatsPack tables. The truncate was not instantaneous (it takes a couple of minutes) and when it is finished, select count(*) from a couple of tables shows there are no rows in the table.  

However, the segments still take as much space as they did before the truncate. I tried truncating a table using the clause drop storage. It finished, and I queried dba_segments again to discover it was still taking the same amount of space. I come in to work this morning and queried dba_segments again. A lot of the space had been released (but not all). So it seems Oracle took its time freeing up extents.  

We are running Oracle on AIX 5. We are using LMT, min extents is 1, initial extent and min_extlen for the tablespace is 64K (assuming the value in dba_tablespaces is in bytes) and segment space management is auto. On the table where I did a manual truncate with the drop storage clause (STATS$SQL_SUMMARY) the min_extents value is 1, and initial extent is 1M. I had tried this very same procedure six months ago, and it worked fine - just as I had expected it to; six months ago we were using Oracle  

Has anybody experienced this type of behavior before with truncate? Any possible explanations?  


Sam Bootsma

George Brown College


416-415-5000 x4933         

The information transmitted is intended solely for the individual or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of or taking action in reliance upon this information by persons or entities other than the intended recipient is prohibited. If you have received this email in error please contact the sender and delete the material from any computer.   

Seeing Beyond Money is a service mark of SunTrust Banks, Inc.

Received on Thu Oct 27 2005 - 08:17:58 CDT

Original text of this message