Re: Users reading from rollback segments

From: Mohammad Rafiq <rafiq9857_at_hotmail.com>
Date: Tue, 26 Feb 2002 12:30:23 -0800
Message-ID: <F001.00419056.20020226123023@fatcity.com>

Is there a way to check if the rollback segment is in use first?

I use following script to see which rollbacks are in use...and then shrink to certain size if it requires to do that...

select substr(a.os_user_name,1,8) "OS User"

,substr(e.username,1,8) "DB User"


, substr(b.object_name,1,30) "Object Name"

, substr(b.object_type,1,10) "Type"
, substr(c.segment_name,1,30) "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

/

Regards
Rafiq

Reply-To: ORACLE-L_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Date: Tue, 26 Feb 2002 11:38:28 -0800

Is there a way to tell if anyone is reading from the rollback segments?

I would like to manually issue 'alter rollback segment XXX shrink;', but do not want to do so if there are users reading read consistent data from the rollback space (thus giving them the ORA-01555 error).

Is there a way to check if the rollback segment is in use first?

Can I try to take it offline? Will it fail if there is someone reading from it?

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Glenn Travis
   INET: Glenn.Travis_at_sas.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


_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp.

-- 
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 Tue Feb 26 2002 - 14:30:23 CST