Re: SQL query to determine the users of undo tablespace

"Mladen Gogala" <mgogala.SPAM-ME.NOT_at_verizon.net> a écrit dans le message de news: pan.2007.04.05.19.58.18_at_verizon.net...
| 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

Yes, rollback segment names are meaningless but the question was about undo tablespace, so I join with dba_rollback_segs to get undo tablespace name of used rollback/undo segments. It was just to complete the answer used blocks/tablespace/user.

(There may be several undo tablespaces in use, if you are in the process of changing it.)

Regards
Michel Cadot Received on Thu Apr 05 2007 - 23:54:49 CDT