| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Rollback segment shrinks
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
Reply-To: ORACLE-L_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
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_at_birch. To: Multiple recipients of
list ORACLE-L <ORACLE-L_at_fatcity.com>
com> cc:
Sent by: Subject: Rollback segment
shrinks
root_at_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: 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: Rajesh.Rao_at_jpmchase.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). 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: rafiq9857_at_hotmail.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:48:32 CDT
 |
 |