David
This query will show sessions using rollback segments:
select s.username, s.sid, rn.name, rs.extents
,rs.status, t.used_ublk, t.used_urec
,do.object_name
from v$transaction t
,v$session s
,v$rollname rn
,v$rollstat rs
,v$locked_object lo
,dba_objects do
where t.addr = s.taddr
and t.xidusn = rn.usn
and rn.usn = rs.usn
and t.xidusn = lo.xidusn(+)
and do.object_id = lo.object_id;
If you offline a rollback segment that a transaction is using, Oracle puts
it in "offline pending" status.
Bigger question: What are you trying to do? Do you feel you have too many
rollback segments? Usually I create a new rollback segment and put it online
before I take one offline.
If you remove a rollback segment, remember to change init.ora. When you
start up again, if Oracle can't find a rollback segment listed in init.ora,
it won't start.
Dennis Williams
DBA, 40%OCP
Lifetouch, Inc.
[EMAIL PROTECTED]
-----Original Message-----
Sent: Tuesday, February 04, 2003 5:24 PM
To: Multiple recipients of list ORACLE-L
I have three rollback segments which all show ONLINE. I want to drop one of
them but I need to know which one should I drop. How do I find out which
one is good to drop?
SVRMGR> select segment_name,owner,tablespace_name,status from
dba_rollback_segs;
SEGMENT_NAME OWNER TABLESPACE_NAME STATUS
------------------------------ ------ ------------------------------ -------
RBS01 PUBLIC RBS ONLINE
RBS02 PUBLIC RBS ONLINE
RBS04 PUBLIC RBS ONLINE
Thanks,
David
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: DENNIS WILLIAMS
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
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).
