From oracle-l-bounce@freelists.org Mon Mar 29 09:15:25 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i2TFFPM16787 for ; Mon, 29 Mar 2004 09:15:25 -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 i2TFFOo16777 for ; Mon, 29 Mar 2004 09:15:24 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 7FF1F634670; Mon, 29 Mar 2004 10:12:07 -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 20663-72; Mon, 29 Mar 2004 10:12:07 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 8DFFD6346A0; Mon, 29 Mar 2004 10:12:03 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Mon, 29 Mar 2004 10:10:48 -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 3A2EC634412 for ; Mon, 29 Mar 2004 10:10:47 -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 20616-36 for ; Mon, 29 Mar 2004 10:10:47 -0500 (EST) Received: from brmea-mail-4.sun.com (brmea-mail-4.Sun.COM [192.18.98.36]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 57B066343FD for ; Mon, 29 Mar 2004 10:10:46 -0500 (EST) Received: from phys-giza-1 ([129.147.4.102]) by brmea-mail-4.sun.com (8.12.10/8.12.9) with ESMTP id i2TFHrMn002303 for ; Mon, 29 Mar 2004 08:17:53 -0700 (MST) Received: from sun.com (sr1-ubrm-18.Central.Sun.COM [129.147.4.63]) by giza-mail1.Central.Sun.COM (iPlanet Messaging Server 5.2 HotFix 1.16 (built May 14 2003)) with ESMTP id <0HVC004YYFV27P@giza-mail1.Central.Sun.COM> for oracle-l@freelists.org; Mon, 29 Mar 2004 08:18:38 -0700 (MST) Date: Mon, 29 Mar 2004 08:18:38 -0700 From: Daniel Fink Subject: Re: [Q] undo tablespace did not release space?? (less quoting) To: oracle-l@freelists.org Message-id: <40683E4E.1FCA1D6E@sun.com> MIME-version: 1.0 X-Mailer: Mozilla 4.79C-CCK-MCD [en] (X11; U; SunOS 5.9 sun4u) Content-Type: text/plain; charset=us-ascii X-Accept-Language: en References: <20040326233813.83463.qmail@web20413.mail.yahoo.com> X-Virus-Scanned: by amavisd-new at freelists.org X-archive-position: 1971 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: Daniel.Fink@Sun.COM Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org The allocation algorithm in 9i is such that a 'full' tablespace is not necessarily a bad thing. When a transaction needs additional undo space, it will acquire space from the existing undo segments, until then it will keep the undo entries around. IIRC, SMON will periodically clean up the undo tablespace. The bottom line is to not worry about a full tablespace, only worry about 1555 errors. As for the automatic v. manual (rollback) debate, at Hotsos a very solid source said that AUM is not recommended for high-volume OLTP systems. As this is not the 'official' Oracle stance, I won't name names, but I respect this person's opinion and find myself in agreement. In the meantime, I suggest you acquire the only guaranteed AUM Management tool...a good bottle of Scotch! Regards, Daniel Paul Drake wrote: > > We have ORACLE 9ir2 running on LINUX server. I > > check > > Tablespace and found undo tablespace almost full for > > long time. It did NOT release space. The undo > > tablespace setup are undo_management=AUTO > > undo_retention=900. Does anyone know why it don't > > release space? > > > > Thanks. > > > After attending Dan's presentation at RMOUG, I'm > thinking of reverting to rollback segments for loading > situations (if not entirely). > > > Pd ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------