Alternately run this querry to check which rollback segments are in use...
set linesize 120
select substr(a.os_user_name,1,8) "OS User"
, substr(b.object_name,1,30) "Object Name"
, substr(b.object_type,1,8) "Type"
, substr(c.segment_name,1,10) "RBS"
, e.process "PROCESS"
, substr(d.used_urec,1,8) "# of Records"
from v$locked_object a
, dba_objects b
, dba_rollback_segs c
, v$transaction d
, v$session e
where a.object_id = b.object_id
and a.xidusn = c.segment_id
and a.xidusn = d.xidusn
and a.xidslot = d.xidslot
and d.addr = e.taddr
/
For shrink use
alter rollback segment rollback_segment_name shrink to 10M;
(or whatever size is allowable in your situation (but it will not be less
then extent_size*min extents)
Regards
Rafiq
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
Date: Wed, 05 Jun 2002 09:13:32 -0800
The rollback segment will not shrink if there are active transactions in
the rollback segment i.e xacts >0 in v$rollstat for that rollback segment.
Check out which transaction is using the rollback segments using
v$transaction where XIDUSN = usn from v$rollname. The ses_addr in
v$transaction should point you to the session which is running this
transaction. Do the honours for that session.
Raj
"Ball, Terry"
<TBall@birch. To: Multiple recipients of
list ORACLE-L <[EMAIL PROTECTED]>
com> cc:
Sent by: Subject: Rollback segment
shrinks
root@fatcity.
com
June 05, 2002
11:53 AM
Please
respond to
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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
MOHAMMAD RAFIQ
_________________________________________________________________
Chat with friends online, try MSN Messenger: http://messenger.msn.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Mohammad Rafiq
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
