From oracle-l-bounce@freelists.org Thu Oct 27 09:04:58 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j9RE4ZLc028767 for ; Thu, 27 Oct 2005 09:04:45 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j9RE4FvX028625 for ; Thu, 27 Oct 2005 09:04:15 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 04773210B16; Thu, 27 Oct 2005 09:04:03 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 16789-09; Thu, 27 Oct 2005 09:04:02 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 6EAF9211259; Thu, 27 Oct 2005 09:04:02 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Thu, 27 Oct 2005 09:02:06 -0500 (EST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 4591A210C28 for ; Thu, 27 Oct 2005 09:02:06 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 16302-02 for ; Thu, 27 Oct 2005 09:02:06 -0500 (EST) Received: from smtp41.singnet.com.sg (smtp41.singnet.com.sg [165.21.103.142]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 9EE11211055 for ; Thu, 27 Oct 2005 09:02:05 -0500 (EST) Received: from chitale.singnet.com.sg (bb220-255-250-199.singnet.com.sg [220.255.250.199]) by smtp41.singnet.com.sg (8.13.5/8.13.5) with ESMTP id j9RE21K4003919; Thu, 27 Oct 2005 22:02:02 +0800 Message-Id: <6.2.1.2.0.20051027220144.01fdf910@pop.singnet.com.sg> Date: Thu, 27 Oct 2005 22:01:57 +0800 To: sbootsma@gbrownc.on.ca, From: Hemant K Chitale Subject: Re: Strange Truncate Table Behaviour In-Reply-To: <4B0639DD5CB28142BFC2CAF1BDE3AB25084D1D87@post.gbrownc.on.c a> References: <4B0639DD5CB28142BFC2CAF1BDE3AB25084D1D87@post.gbrownc.on.ca> Mime-Version: 1.0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by Ecartis X-archive-position: 27679 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: hkchital@singnet.com.sg Precedence: normal Reply-To: hkchital@singnet.com.sg X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-mailscan-MailScanner-Information: Please contact the ISP for more information X-mailscan-MailScanner: Found to be clean X-MailScanner-From: oracle-l-bounce@freelists.org X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on air891.startdedicated.com X-Spam-Level: X-Spam-Status: No, hits=-4.4 required=5.0 tests=BAYES_00,HTML_FONTCOLOR_BLUE, HTML_MESSAGE,MIME_HTML_ONLY autolearn=no version=2.63
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