RE: Users reading from rollback segments

Thanks for the replies. I have all sorts of neat queries (which I can post) which show me gobs of information about my rollback segments (sizes, extents, optimal, shrinks, active transactions, block used by those transcations, ad infinitum...).

HOWEVER, I still cannot find an answer to my original question; "Is there a way to tell if anyone is reading from the rollback segments?"

Readers do not open transactions, correct? So they will not show up on the queries most of us are running against v$rollxxx and v$transaction. Where can I find out of someone is using the undo info in the rollbacks for read consistency? In other words, how do I find the readers (from rollback, not from the tables themselves)?

I do not want to issue a shrink (and thus risk a ORA-01555) if people are still using the rollback for read consistency.

To answer another reply's question: I am shrinking the rollbacks right before I run a large batch job, so as to give the job the maximum amount of space in the rollback tablespace. (I cannot utilize 'set transaction use ...' as this is an Oracle Apps job which actually does many transactions (re: purges)).

> -----Original Message-----
> From: Glenn Travis [mailto:Glenn.Travis_at_sas.com]
> Sent: Tuesday, February 26, 2002 2:38 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Users reading from rollback segments
>
>
> 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: 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).