From oracle-l-bounce@freelists.org Wed Apr 20 09:53:20 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j3KErJ6h003965 for ; Wed, 20 Apr 2005 09:53:20 -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 j3KErJ4Z003961 for ; Wed, 20 Apr 2005 09:53:19 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id AB850185CDF; Wed, 20 Apr 2005 08:51: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 07594-08; Wed, 20 Apr 2005 08:51:00 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 3395E185C85; Wed, 20 Apr 2005 08:51:00 -0500 (EST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=ARCOeOVfu1dHTG3rmFRAsyC4qFM61Y4CxABuHFJm1tzHFiDcn2wHFMvp1uTrT55T6ALYipiQzbRslvr3AKL0jBGrCGG8uJX/tDzeeAOR0dCdWESFa/s25kd6wLJ7Z8Hp+j4kgLx6DeMIrutybqaZLYJJfSRXba130xlgiJibrbo= Message-ID: Date: Wed, 20 Apr 2005 08:49:10 -0500 From: Greg Norris To: ORACLE-L Subject: Re: tempspace usage In-Reply-To: Mime-Version: 1.0 Content-type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit Content-Disposition: inline References: X-archive-position: 18662 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: spikey.mcmarbles@gmail.com Precedence: normal Reply-To: spikey.mcmarbles@gmail.com X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on air891.startdedicated.com X-Spam-Status: No, hits=0.0 required=5.0 tests=none autolearn=ham version=2.63 > If the problem is the space doesn't disappear from V$SORT_USAGE > across multiple statements, I'd bet it was temp tables/indexes > because a sort or hash should disappear when the statement ends > while the temp table persists until the end of the transaction or session > depending on how its scope was declared. Yes, it definately appears to be persisting across individual statements. Unfortunately this database has no global temporary tables (aside from SYS.ATEMPTAB$, of course), so that isn't it. :-( > Also check the SEGTYPE column to see if the space is being used for > sort, hash or temporary tables/indexes. Thanx, this was a great suggestion. It seems that all of the problematic sessions are split between LOB_DATA and LOB_INDEX, with the former taking the majority of the space. I'm guessing this means that the application is using temporary LOBS, and not releasing them once they're no longer needed... I'll check with the developers. Thanx! --=20 "I'm too sexy for my code." - Awk Sed Fred. -- http://www.freelists.org/webpage/oracle-l