Hi,
If you
want to drop one of the the RBS, First you have to check in the application
level. Which user that used the rollback segment.
select
username,name,used_ublk,
start_time,sum(waits),sum(gets),
sum(waits)*100/sum(gets)
from v$transaction,v$rollname,v$session,v$rollstat
where xidusn=v$rollname.usn and
v$transaction.ses_addr = v$session.saddr
and v$rollstat.usn = v$rollname.usn
group by username,name,used_ublk,start_time
start_time,sum(waits),sum(gets),
sum(waits)*100/sum(gets)
from v$transaction,v$rollname,v$session,v$rollstat
where xidusn=v$rollname.usn and
v$transaction.ses_addr = v$session.saddr
and v$rollstat.usn = v$rollname.usn
group by username,name,used_ublk,start_time
After
the execution the script above you can findout which rollback segment which not
used by user. and you can drop that rollback segment.
alter
rollback segment rollback_segment_name offline;
alter
rollback segment rollback_segment_name drop;
Thank's
Bernardus Deddy Hoeydiono.
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Nguyen, David M
Sent: Wednesday, February 05, 2003 6:24 AM
To: Multiple recipients of list ORACLE-L
Subject: Which rollback segment is currently active?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
