Return-Path: Received: from newsfeed.cts.com (newsfeed.cts.com [209.68.248.164]) by naude.co.za (8.11.2/8.11.2) with SMTP id g560dST03413 for ; Wed, 5 Jun 2002 20:39:28 -0400 Received: from fatcity.UUCP (uucp@localhost) by newsfeed.cts.com (8.9.3/8.9.3) with UUCP id JAA32877; Wed, 5 Jun 2002 09:37:24 -0700 (PDT) Received: by fatcity.com (26-Feb-2001/v1.0g-b71/bab) via UUCP id 0047541D; Wed, 05 Jun 2002 09:33:38 -0800 Message-ID: Date: Wed, 05 Jun 2002 09:33:38 -0800 To: Multiple recipients of list ORACLE-L X-Comment: Oracle RDBMS Community Forum X-Sender: Richard Huntley Sender: root@fatcity.com Reply-To: ORACLE-L@fatcity.com Errors-To: ML-ERRORS@fatcity.com From: Richard Huntley Subject: RE: Rollback segment shrinks Organization: Fat City Network Services, San Diego, California X-ListServer: v1.0g, build 71; ListGuru (c) 1996-2001 Bruce A. Bergman Precedence: bulk Mime-Version: 1.0 Content-Type: multipart/mixed; boundary="----=_NextPartTM-000-5f2f3e2f-7890-11d6-8b0a-00b0d04949c1" ------=_NextPartTM-000-5f2f3e2f-7890-11d6-8b0a-00b0d04949c1 Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C20CAD.AC6DD5B0" ------_=_NextPart_001_01C20CAD.AC6DD5B0 Content-Type: text/plain; charset="iso-8859-1" Terry, This query will tell you which process is using which rollback segment, maybe that'll help you backtrack to find out what's going on. column "Oracle UserName" FORMAT a15 column "RBS Name" format a15 select r.name "RBS Name", p.spid, l.sid "ORACLE PID", s.username "Oracle UserName" from v$lock l, v$process p, v$rollname r, v$session s where s.sid = l.sid and l.sid = p.pid(+) and r.usn = trunc(l.id1(+)/65536) and l.type(+) = 'TX' and l.lmode(+) = 6 order by r.name / Then go into Instance Mananger and take a look at what sql is being executed and when you find the one that's causing the problem, kill it. HTH, Rich -----Original Message----- Sent: Wednesday, June 05, 2002 12:34 PM To: Multiple recipients of list ORACLE-L Terry, Take a look to see if there is some long running process that is inactive and attached to a client that you know is not connected to the database. I've had this happen when someone lets the query from hell loose and then shuts off their PC or worse yet, does a 'set pause on' in their SQL session and minimizes SQL*Plus. Your rollback usage will grow logarithmically since the query is forcing it to be retained. Dick Goulet ____________________Reply Separator____________________ Author: "Ball; Terry" Date: 6/5/2002 7:53 AM Oracle 8.1.6.3 on Sun Solaris 2.6. The rollback tablespace filled up last night and the rollback segments became full. I added space to the tablespace and tried shrinking the rollback segments. They remained full, so I altered them offline and online. The extents are increasing, but I still can not get a shrink to work. If they don't stop increasing, my tablespace will fill up again and I can't keep throwing disk at it. Since this is a production system, bouncing is not an option. Any ideas? TIA Terry Terry Ball, DBA Birch Telecom Work: 816-300-1335 FAX: 816-300-1800 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Ball, Terry INET: TBall@birch.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: dgoulet@vicr.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). ------_=_NextPart_001_01C20CAD.AC6DD5B0 Content-Type: text/html; charset="iso-8859-1" RE: Rollback segment shrinks

Terry,

This query will tell you which process is using which rollback segment, maybe
that'll help you backtrack to find out what's going on.

column "Oracle UserName" FORMAT a15
column "RBS Name" format a15
select r.name "RBS Name", p.spid, l.sid "ORACLE PID",
        s.username "Oracle UserName"
from v$lock l, v$process p, v$rollname r, v$session s
where s.sid = l.sid and l.sid = p.pid(+)
and r.usn = trunc(l.id1(+)/65536)
and l.type(+) = 'TX' and l.lmode(+) = 6
order by r.name
/

Then go into Instance Mananger and take a look at what sql is being executed
and when you find the one that's causing the problem, kill it.

HTH,
Rich


-----Original Message-----
From: dgoulet@vicr.com [mailto:dgoulet@vicr.com]
Sent: Wednesday, June 05, 2002 12:34 PM
To: Multiple recipients of list ORACLE-L
Subject: Re:Rollback segment shrinks


Terry,

    Take a look to see if there is some long running process that is inactive
and attached to a client that you know is not connected to the database.  I've
had this happen when someone lets the query from hell loose and then shuts off
their PC or worse yet, does a 'set pause on' in their SQL session and minimizes
SQL*Plus.  Your rollback usage will grow logarithmically since the query is
forcing it to be retained.

Dick Goulet

____________________Reply Separator____________________
Author: "Ball; Terry" <TBall@birch.com>
Date:       6/5/2002 7:53 AM

Oracle 8.1.6.3 on Sun Solaris 2.6.

The rollback tablespace filled up last night and the rollback segments
became full.  I added space to the tablespace and tried shrinking the
rollback segments.  They remained full, so I altered them offline and
online.  The extents are increasing, but I still can not get a shrink to
work.  If they don't stop increasing, my tablespace will fill up again and I
can't keep throwing disk at it.  Since this is a production system, bouncing
is not an option.

Any ideas?

TIA

Terry

Terry Ball, DBA
Birch Telecom
Work: 816-300-1335
FAX:  816-300-1800

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Ball, Terry
  INET: TBall@birch.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
  INET: dgoulet@vicr.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

------_=_NextPart_001_01C20CAD.AC6DD5B0-- ------=_NextPartTM-000-5f2f3e2f-7890-11d6-8b0a-00b0d04949c1-- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Richard Huntley INET: rhuntley@mindleaders.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru@fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).