Re: Rollback segment space usage

From: Mohammad Rafiq <rafiq9857_at_hotmail.com>
Date: Wed, 23 Oct 2002 09:40:59 -0800
Message-ID: <F001.004F154B.20021023094059@fatcity.com>

George,

Try this query and check for average_active

select ud.name,

       sg.extents ext,
       round(sg.blocks * ts.blocksize / 1048576, 2) MB,
       round(s.optsize / 1048576, 1) optsize,
       round(s.aveactive / 1048576, 1) AVEACT,
       round(s.hwmsize / 1048576, 1) HWMSIZE,
       s.shrinks,
       s.wraps,
       s.waits,
       nvl(s.status, 'OFFLINE') status
from v$rollstat s,
     sys.undo$ ud,
     sys.seg$ sg,
     sys.ts$ ts
where ud.us# = s.usn (+)
  and ud.file# = sg.file#

  and ud.block# = sg.block#
  and sg.ts# = ts.ts#
/

Regards
Rafiq

Reply-To: ORACLE-L_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Date: Wed, 23 Oct 2002 09:02:17 -0800

Hi all,

Trying here now since have tried to find it online and build the query myself.

I need to figure out how much space a user/session is using of a rollback segment.

I got the queries to show which segment the user is attached to but need to know how much is being used (something like a fuel gauge).

Any takers.

George

---

George Leonard
Oracle Database Administrator
Dimension Data (Pty) Ltd
(Reg. No. 1987/006597/07)

Tel: (+27 11) 575 0573
Fax: (+27 11) 576 0573
E-mail:george.leonard_at_za.didata.com
Web: http://www.didata.co.za

You Have The Obligation to Inform One Honestly of the risk, And As a Person You Are Committed to Educate Yourself to the Total Risk In Any Activity! Once Informed & Totally Aware of the Risk, Every Fool Has the Right to Kill or Injure Themselves as They See Fit!

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Leonard, George
   INET: george.leonard_at_farnell.com

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).


_________________________________________________________________
Protect your PC - get McAfee.com VirusScan Online 
http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963

-- 
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 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 Oct 23 2002 - 12:40:59 CDT