RE: Strange Truncate Table Behaviour

Compare the results of dba_segments to dba_extents. I know with
partitions on 9.2.0.6 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

3-9446

 

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Sam Bootsma
Sent: Thursday, October 27, 2005 9:06 AM
To: oracle-l@xxxxxxxxxxxxx
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 9.2.0.6 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 9.2.0.4.  

 

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

 

Thanks,

 

 

Sam Bootsma

George Brown College

 <mailto:sbootsma@xxxxxxxxxxxxx> sbootsma@xxxxxxxxxxxxx

416-415-5000 x4933 
  
  
  
LEGAL DISCLAIMER 
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. 
[ST:XCL] 
 
 
 
 

Other related posts: