From oracle-l-bounce@freelists.org Fri Nov 5 07:49:48 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id iA5Dnmo02286 for ; Fri, 5 Nov 2004 07:49:48 -0600 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id iA5DnjI02264 for ; Fri, 5 Nov 2004 07:49:46 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 215AB72D12C; Fri, 5 Nov 2004 08:56:00 -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 04414-68; Fri, 5 Nov 2004 08:56:00 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 71C1172DA1B; Fri, 5 Nov 2004 08:55:56 -0500 (EST) Message-ID: <01cf01c4c33e$ca37a6d0$6600a8c0@GROUCHO> From: "Henry Poras" To: , Cc: "HANDM@polaroid.com" , References: <564DE4477544D411AD2C00508BDF0B6A2133DF81@usahm018.exmi01.exch.eds.com> Subject: Re: LMT's with autoallocate Date: Fri, 5 Nov 2004 08:53:09 -0500 MIME-Version: 1.0 Content-type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 X-archive-position: 11957 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: hporas@comcast.net Precedence: normal Reply-To: hporas@comcast.net X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org I've occasionally used it as an easy method of saving space with PeopleSoft. PeopleSoft has a standard set of tablespaces and depending on the modules in use a lot of its tables and indexes remain empty. Instead of creating new tablespaces (a customization), autoallocate uses the smallest extent size for the empty objects. Less space is wasted. >From a note I posted to another site about a year ago: " Quick example of the space difference between UNIFORM and AUTOALLOCATE LMTs. I recently build a SYS database (Tools 8.42) for PeopleSoft Financials (8.40). Just looking at PSINDEX tablespace, I created it with EXTENT MANAGEMENT LOCAL UNIFORM SIZE 100K. The datafile also had AUTOEXTEND on, using the default extend of one block at a time (yeah it was slow, but that didn't matter for this test). After building the tablespace using uniform extents, I created a new LMT tablespace (PSINDEX_TMP) using AUTOALLOCATE. I then rebuilt all of my indexes from PSINDEX into PSINDEX_TMP. (at the end, I dropped and recreated PSINDEX and rebuilt everything back there to keep PeopleSoft happy). The size of the original data file for psindex was ~2.6G (2,648,391,680 bytes). After rebuilding stuff into psindex_tmp, the size was ~ 2G (2,021,343,232 bytes). So if there is a space limitation, autoallocate is definitely the way to go." Henry ----- Original Message ----- From: "Jared Still" To: Cc: "HANDM@polaroid.com" ; Sent: Thursday, November 04, 2004 6:00 PM Subject: Re: LMT's with autoallocate > I don't believe that anyone has actually supplied > sufficient reason for using autoallocate. > > Why bother if it can run out of space, even though > space is available? > > Shades of DD TBS fragmentation... > > Jared > > > > On Thu, 4 Nov 2004 16:35:14 -0500, Powell, Mark D wrote: > > I saw a demo of this where there were no 64M extents so the object took 8M > > extents and then errored out even though there were still 8M extents left > > after it took so many of them, maybe 128. I do not think that I saved the > > post. > > > > Oracle has stated that the logic behind auto-allocate is subject to change > > without notice so observed behavior may not survive an upgrade. > > > > HTH -- Mark D Powell -- > > > > > > > > > > -----Original Message----- > > From: oracle-l-bounce@freelists.org > > [mailto:oracle-l-bounce@freelists.org]On Behalf Of Hand, Michael T > > Sent: Thursday, November 04, 2004 4:14 PM > > To: Oracle-L (oracle-l@freelists.org) > > Subject: LMT's with autoallocate > > > > I understand the change in extent size as segments get larger in LMT's with > > autoallocate, but read on a 3rd-party application web site that the smaller > > extent sizes (8M, 1M, 64K) are used if there isn't enough freespace for 64M > > extent. My experiments so far (using allocate extent and table insertion) > > have shown that if there is not 64M of freespace, the process errors out. > > Has anybody heard of this "feature", and under what conditions it manifests > > itself? Or is this unnamed 3rd party smoking something? The platform is > > Tru64 using 9.2.0.5. > > > > Thanks, > > Mike Hand > > > > -- > > This transmission is intended only for use by the addressee(s) named herein > > and may contain information that is proprietary, confidential and/or legally > > privileged. If you are not the intended recipient, you are hereby notified > > that any disclosure, copying, distribution, or use of the information > > contained herein (including any reliance thereon) is STRICTLY PROHIBITED. If > > you received this transmission in error, please immediately contact the > > sender and destroy the material in its entirety, whether in electronic or > > hard copy format. Thank you. > > > > -- > > http://www.freelists.org/webpage/oracle-l > > -- > > http://www.freelists.org/webpage/oracle-l > > > > > -- > Jared Still > Certifiable Oracle DBA and Part Time Perl Evangelist > -- > http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l