Oracle-L: RE: Rollback segment shrinks

From: Richard Huntley <rhuntley_at_mindleaders.com>
Date: Wed, 05 Jun 2002 09:13:33 -0800
Message-ID: <F001.0047536F.20020605091333@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
/

HTH,
Rich

-----Original Message-----
Sent: Wednesday, June 05, 2002 12:23 PM
To: Multiple recipients of list ORACLE-L

Terry,

Can you see if there are any session(s) (runaway or not) that might be causing your rollback segments to fill up? Until you can figure out what sessions are filling up the rollbacks you will be stuck adding more space until those session(s) end.

Bryan

-----Original Message-----
Sent: Wednesday, June 05, 2002 11:53 AM
To: Multiple recipients of list ORACLE-L

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: Rodrigues, Bryan
INET: BRodrigues_at_elcom.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

--

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
--------------------------------------------------------------------