From oracle-l-bounce@freelists.org Tue Apr 6 23:28:27 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i374SRs26521 for ; Tue, 6 Apr 2004 23:28:27 -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 i374SRo26515 for ; Tue, 6 Apr 2004 23:28:27 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 49DBA6345A3; Tue, 6 Apr 2004 23:23:56 -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 02162-84; Tue, 6 Apr 2004 23:23:56 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 90239634558; Tue, 6 Apr 2004 23:23:55 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Tue, 06 Apr 2004 23:22: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 D9D78634170 for ; Tue, 6 Apr 2004 23:22: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 03587-02 for ; Tue, 6 Apr 2004 23:22:47 -0500 (EST) Received: from gtllimited.com (unknown [202.140.131.20]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with SMTP id 551CB6345A3 for ; Tue, 6 Apr 2004 23:22:46 -0500 (EST) Received: (qmail 3640 invoked by uid 504); 7 Apr 2004 04:51:50 -0000 Received: from dilipc@gtllimited.com by mail2.gtllimited.com by uid 501 with qmail-scanner-1.16 (clamscan: 0.60. spamassassin: 2.60. Clear:SA:0(-4.8/5.0):. Processed in 82.826502 secs); 07 Apr 2004 04:51:50 -0000 X-Spam-Status: No, hits=-4.8 required=5.0 Received: from unknown (HELO dilipc) (mail@10.102.3.107) by gtllimited.com with SMTP; 7 Apr 2004 04:50:27 -0000 From: "Dilip Chavan" To: Subject: RE: Rollback Segment Trans Date: Wed, 7 Apr 2004 09:54:50 +0530 Message-ID: MIME-Version: 1.0 Content-type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2910.0) X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6600 In-Reply-To: <0e9f01c41c56$d8daa730$a00100ac@awc.com> Importance: Normal X-Virus-Scanned: by amavisd-new at freelists.org X-archive-position: 2662 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: dilipc@gtllimited.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org Hi, Check v$rollstat which has a column XACTS which shows numbers of transactions using that perticular rollback segments. Plus you can check v$transaction. It has a column XIDUSN. It shows which rollback segment that perticular transaction is using. As you said you killed a long running delete statement. Check USED_UBLK column of v$transaction for that perticular transaction. If the value is decreasing contiously, then PMON is still rolling back your statement. Once PMON is done with it, you can shrink the rollback segment. ~Dilip -----Original Message----- From: oracle-l-bounce@freelists.org [mailto:oracle-l-bounce@freelists.org]On Behalf Of Fahd Mirza Sent: Wednesday, April 07, 2004 9:45 AM To: oracle-l@freelists.org Subject: Rollback Segment Trans Hi experts, I just killed a very long session which wasn't responding and was inactive for 2 days. The session was performing a delete operation on a table. After I killed the session, I noticed that the size of one rollback segment became very large, the optimal value is 4M and the current size of the particular rollback segment is 500M. I have tried to shrink rollback segment manually like this alter rollback segment rbs25 shrink to 4M; but in vain. what should I do? how could it be known that a particular rollback segment is using currently which transactions? Thanks in advance, regards. Fahd ---------------------------------------------------------------- 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 ----------------------------------------------------------------- ---------------------------------------------------------------- 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 -----------------------------------------------------------------