From oracle-l-bounce@freelists.org Thu Oct 27 08:17:58 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j9RDHhTk016785 for ; Thu, 27 Oct 2005 08:17:48 -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 j9RDHTvX016712 for ; Thu, 27 Oct 2005 08:17:35 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id B6FA12112A7; Thu, 27 Oct 2005 08:17: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 10681-10; Thu, 27 Oct 2005 08:17:06 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 278002112A2; Thu, 27 Oct 2005 08:17:06 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Thu, 27 Oct 2005 08:15:11 -0500 (EST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id BD07C2110D3 for ; Thu, 27 Oct 2005 08:15:11 -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 10336-02 for ; Thu, 27 Oct 2005 08:15:11 -0500 (EST) Received: from ga016a0c2.suntrust.com (mx5.suntrust.com [167.181.33.37]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 653B921127B for ; Thu, 27 Oct 2005 08:15:11 -0500 (EST) Received: from unknown (HELO GA016A0E10.corp.suntrust.com) (10.49.90.110) by ga016a0c2.suntrust.com with ESMTP; 27 Oct 2005 09:57:44 -0400 X-IronPort-AV: i="3.97,258,1125892800"; d="scan'208,217"; a="231292691:sNHT28445724" Received: from VA016A0E0.corp.suntrust.com ([10.15.164.100]) by GA016A0E10.corp.suntrust.com with Microsoft SMTPSVC(5.0.2195.6713); Thu, 27 Oct 2005 09:15:07 -0400 Received: from VA016A0E2.corp.suntrust.com ([10.15.164.102]) by VA016A0E0.corp.suntrust.com with Microsoft SMTPSVC(5.0.2195.6713); Thu, 27 Oct 2005 09:15:01 -0400 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1441 content-class: urn:content-classes:message MIME-Version: 1.0 Priority: normal Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C5DAF8.6ABF22E4" Subject: RE: Strange Truncate Table Behaviour Date: Thu, 27 Oct 2005 09:14:52 -0400 Message-ID: <52C70FF150F49E479DAF59C68A27149D01F0E1AC@va016a0e2.corp.suntrust.com> X-MS-Has-Attach: X-MS-TNEF-Correlator: Thread-Topic: Strange Truncate Table Behaviour Thread-Index: AcXa9zSxwA8j6wkzRB2WQAuKGCt49gAAIq2Q From: "Kline.Michael" To: , X-OriginalArrivalTime: 27 Oct 2005 13:15:01.0333 (UTC) FILETIME=[6FDB8850:01C5DAF8] X-archive-position: 27675 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: Michael.Kline@SunTrust.com Precedence: normal Reply-To: Michael.Kline@SunTrust.com 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.6 required=5.0 tests=AWL,BAYES_00,HTML_50_60, HTML_FONTCOLOR_UNKNOWN,HTML_MESSAGE autolearn=no version=2.63 ------_=_NextPart_001_01C5DAF8.6ABF22E4 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable 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. =20 Michael Kline Database Administration Outside 804.261.9446 Cell 804.744.1545 3-9446 =20 -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Sam Bootsma Sent: Thursday, October 27, 2005 9:06 AM To: oracle-l@freelists.org Subject: Strange Truncate Table Behaviour =20 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. =20 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. =20 =20 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. =20 =20 Has anybody experienced this type of behavior before with truncate? Any possible explanations? =20 Thanks, =20 =20 Sam Bootsma George Brown College sbootsma@gbrownc.on.ca 416-415-5000 x4933=20 =20 =20 =20 LEGAL DISCLAIMER=20 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.=20 =20 Seeing Beyond Money is a service mark of SunTrust Banks, Inc.=20 [ST:XCL]=20 =20 =20 =20 =20 ------_=_NextPart_001_01C5DAF8.6ABF22E4 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

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@freelists.org [mailto:oracle-l-bounce@freelists.org] = On Behalf Of Sam Bootsma
Sent: Thursday, October = 27, 2005 9:06 AM
To: oracle-l@freelists.org
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

sbootsma@gbrownc.on.ca

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]
------_=_NextPart_001_01C5DAF8.6ABF22E4-- -- http://www.freelists.org/webpage/oracle-l