RE: Users reading from rollback segments

XACTS is the NUMBER of active transactions in the rollback segment. 0 or > 0. However, I am not looking for transactions, I am looking for queries reading from the rollback space for read consistency purposes.

I found an answer to my second question and it is "NO". You can take the rollback segment offline and it will succeed if it is being used. It will just go into an 'OFFLINE PENDING' state until all users are out. So I don't think I can use this logic. Question: How does Oracle know it is in 'use'? What does in 'use' mean? Active transactions plus readers? I would not think Oracle would take a rollback segment offline if users are reading from it (this doesn't necessarily mean they are holding active transactions). So if Oracle knows it is 'in use', how can I get that info too?

> -----Original Message-----
> From: Joan Hsieh [mailto:joan.hsieh_at_tufts.edu]
> Sent: Tuesday, February 26, 2002 3:51 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: Users reading from rollback segments
>
>
> SELECT segment_name,
> USN,EXTENTS,RSSIZE,HWMSIZE,optsize,xacts,aveactive,WRAPS,shrinks FROM
> V$ROLLSTAT,
> dba_rollback_segs
> where usn=segment_id
> xacts =1 is in use. otherwise it is 0.
> oracle 8.1.6 you can take an active rollback segment offline, but also
> kill the transaction. 8.17 supposed to fix the bug. I assume
> it will let
> the transaction finished.
>
> Joan
>
> Glenn Travis wrote:
> >
> > 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).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Joan Hsieh
> INET: joan.hsieh_at_tufts.edu
>
> 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: 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).