From oracle-l-bounce@freelists.org Thu Sep 2 10:37:21 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i82FbL116741 for ; Thu, 2 Sep 2004 10:37:21 -0500 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 i82FbLI16736 for ; Thu, 2 Sep 2004 10:37:21 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 2674572C219; Thu, 2 Sep 2004 10:18: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 32333-35; Thu, 2 Sep 2004 10:18:05 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id C62AC72F8FD; Thu, 2 Sep 2004 08:41:43 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Thu, 02 Sep 2004 08:39:57 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id E5ED172CD0A for ; Thu, 2 Sep 2004 08:30:21 -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 13058-49 for ; Thu, 2 Sep 2004 08:30:21 -0500 (EST) Received: from mproxy.gmail.com (rproxy.gmail.com [64.233.170.199]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id C5DB672E2CC for ; Thu, 2 Sep 2004 08:03:44 -0500 (EST) Received: by mproxy.gmail.com with SMTP id 79so350201rnl for ; Thu, 02 Sep 2004 06:06:56 -0700 (PDT) Received: by 10.38.164.76 with SMTP id m76mr2798448rne; Thu, 02 Sep 2004 06:06:55 -0700 (PDT) Received: by 10.38.78.55 with HTTP; Thu, 2 Sep 2004 06:06:53 -0700 (PDT) Message-ID: <7765c89704090206067315ee44@mail.gmail.com> Date: Thu, 2 Sep 2004 14:06:53 +0100 From: Niall Litchfield To: oracle-l@freelists.org Subject: Re: ORA-03232 In-Reply-To: <002101c490d6$d063d0a0$6203a8c0@internethall> Mime-Version: 1.0 Content-type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 8bit References: <002101c490d6$d063d0a0$6203a8c0@internethall> X-Virus-Scanned: by amavisd-new at freelists.org X-archive-position: 8942 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: niall.litchfield@gmail.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org On Thu, 2 Sep 2004 15:22:56 +0500, fahd mirza wrote: > Dear Listers, > I created a locally managed tablespace with the following command, > > CREATE TABLESPACE pms DATAFILE 'G:\ORACLE\ORADATA\VIEW\pms.DBF' SIZE 200M AUTOEXTEND ON EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K; > > and then imported some tables, which occupied approximate 99% space in the tablespace. But when a users tried to run queries on the tables, it gave the following error, > > ORA-03232 unable to allocate an extent of string blocks from tablespace string > > Cause: An attempt was made to specify a HASH_MULTIBLOCK_IO_COUNT value that is greater than the tablespace's NEXT value. > > Action: Increase the value of NEXT for the tablespace using ALTER TABLESPACE DEFAULT STORAGE or decrease the value of HASH_MULTIBLOCK_IO_COUNT. > > As according to Oracle documentation, When you allocate a locally managed tablespace, you cannot specify default storage parameters or minimum extent size. So I cant increase the value of NEXT, also user settings for HASH_MULTIBLOCK_IO_COUNT are highly un-recommended by Oracle,besides its complex enough. > > The rest of the schemas were analyzed but not this fresh one. The queries were also joining the tables from the other schemas. when I also analyzed this fresh schema, the error disappeared and everything was again fine. > Would please somone clarify what happened actually? Most likely the string value in tablespace string referred to your temp tablespace where you were required to sort, and not to the new data ts. When you got appropriate stats, you got appropriate plans that didn't require sorting/excessive temp ts use :) . As a note since you were joining to tables that did have stats, and assuming a not so recent version (I'd hazard a guess this was some sort of 8i) so you don't get dynamic sampling, you will be using the CBO and the CBO will use default values for the stats (100 row tables in very little space IIRC). -- Niall Litchfield Oracle DBA http://www.niall.litchfield.dial.pipex.com --- To unsubscribe - mailto:oracle-l-request@freelists.org&subject=unsubscribe To read recent messages - http://freelists.org/archives/oracle-l/09-2004