Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Rollback segment shrinks

RE: Rollback segment shrinks

From: Richard Huntley <rhuntley_at_mindleaders.com>
Date: Wed, 05 Jun 2002 09:33:38 -0800
Message-ID: <F001.0047541D.20020605093338@fatcity.com>


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" <TBall_at_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_at_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_at_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_at_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_at_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: Richard Huntley
  INET: rhuntley_at_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_at_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). Received on Wed Jun 05 2002 - 12:33:38 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US