Re: SQL query to determine the users of undo tablespace

On Thu, 05 Apr 2007 19:02:52 +0200, Michel Cadot wrote:

> "yaru22" <yaru22_at_gmail.com> a écrit dans le message de news:
> 1175789083.986357.255780_at_d57g2000hsg.googlegroups.com... | How do I
> determine which users are using undo tablespace in an Oracle | 9.2
> database?
> |
> | Thanks.
> |
>
> Join dba_segments (or dba_rollback_segs), v$rollstat, v$rollname,
> v$session and v$transaction.

There is an article about that on JL Consultancy cooperative FAQ, written by Mark Powell:
http://www.jlcomp.demon.co.uk/faq/active_rollback.html

The article is obsolete and intended only for oracle7 & 8. As you know, with Oracle9i, rollback segments are hidden in the UNDO tablespace, so the segment names are meaningless. Also, in 9i and above, v$transaction shows you how many undo blocks are being used by the transaction ("used_ublk" column). I have somewhat simpler query of my own, which is not as detailed as Mark's:

SELECT sess.username,

         sess.sid,sess.serial# serial,p.spid "System PID",
         sess.program,sess.osuser,sess.machine,
         t.used_ublk "Undo blocks",t.status "Trans. Status",
         to_char(logon_time,'MM/DD/YYYY HH24:MI') "Logged In"
        FROM v$session sess,v$transaction t,v$process p
        WHERE  sess.saddr=t.ses_addr and
        sess.paddr=p.addr and
        ORDER BY t.status,t.used_ublk desc;

-- 
http://www.mladen-gogala.com