| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Users reading from rollback segments
Here is the Query you are looking for.
set lines 132
set pages 30
col rr heading 'RB Segment' format a18 col os heading 'OS User' format a10 col te heading 'Terminal' format a10
Fax : (713) 438-6825 Cell : (713) 539-1375
-----Original Message-----
Sent: Tuesday, February 26, 2002 3:03 PM
To: Multiple recipients of list ORACLE-L
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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Gupta, Brijesh INET: Brijesh.Gupta_at_Airliquide.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 Wed Feb 27 2002 - 15:45:13 CST
 |
 |