Oracle-L: RE: Which rollback segment is currently active?

From: Bernardus Deddy Hoeydiono <deddy_at_sdi.co.id>
Date: Wed, 05 Feb 2003 00:43:43 -0800
Message-ID: <F001.0054401C.20030205004343@fatcity.com>

Which rollback segment is currently active?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

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: root_at_fatcity.com [mailto:root_at_fatcity.com]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

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Bernardus Deddy Hoeydiono
INET: deddy_at_sdi.co.id

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: 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 Feb 05 2003 - 02:43:43 CST