Could be Bug 4142932 (see Note 4142932.8) :
DBA_SEGMENTS.EXTENTS wrong for locally managed
segment after TRUNCATE operation
They have also shown a test case in the note :
This problem is introduced in 9.2.0.6 by the fix for bug
3338673.
The value returned from DBA_SEGMENTS.EXTENTS can be wrong
for a segment in a locally managed tablespace after a TRUNCATE
operation on that segment.
eg:
CREATE TABLE A ( A NUMBER ); /* In a LOCALLY MANAGED
TABLESPACE */
ALTER TABLE a allocate extent;
ALTER TABLE a allocate extent;
ALTER TABLE a allocate extent;
TRUNCATE TABLE a;
SELECT * FROM DBA_SEGMENTS WHERE SEGMENT_NAME='A';
^
Shows an EXTENTS value of 4 rather than 1 (as truncated
to)
At 09:06 PM Thursday, Sam Bootsma wrote:
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
sbootsma@gbrownc.on.ca
416-415-5000 x4933
Hemant K Chitale
http://web.singnet.com.sg/~hkchital
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Oct 27 2005 - 09:04:58 CDT
