Re: What sql is using undo tablespace

From: sumit Tyagi <dba.tyagisumit_at_gmail.com>
Date: Tue, 13 May 2014 14:16:20 +0530
Message-ID: <CAFW4NVy6MRvaGhGh741gg94i8i9+ohvbSsoYT368G66sZC3Wbw_at_mail.gmail.com>

undo is just generated by "a query", we do not track undo at a query level undo is generated by transactions

v$transaction has a column used_ublk that tells you how much undo a given transaction has
generated....

to figure out how much undo (blocks or MB) a user session is using as of a "point in time" sure you can find :

as soon as they commit, v$transaction empties for them.

so to find information use v$sesstat joined to v$statname, look for name like '%undo%', you'll find useful information there - it is cumulative for a session.

select s.sid,

       s.username,
       sum(ss.value) / 1024 / 1024 as undo_size_mb

from v$sesstat ss
  join v$session s on s.sid = ss.sid
  join v$statname stat on stat.statistic# = ss.statistic# where stat.name = 'undo change vector size' and s.type <> 'BACKGROUND'
and s.username IS NOT NULL
group by s.sid, s.username;

--
http://www.freelists.org/webpage/oracle-l

Received on Tue May 13 2014 - 10:46:20 CEST