OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » Undo space

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Undo space [message #539402]

Sun, 15 January 2012 01:08

jack55
Messages: 1
Registered: November 2011

Junior Member

Hello,

Can anyone help me with a query to find out who is eating up most of the undo space and what query he is running..

Thanks in advance

Report message to a moderator

Re: Undo space [message #539404 is a reply to message #539402]

Sun, 15 January 2012 01:24

John Watson
Messages: 8929
Registered: January 2010
Location: Global Village

Senior Member

Quote:

who is eating up most of the undo space and what query he is running..

select u.username,t.used_ublk,s.sql_text
from v$session u join v$transaction t on (u.saddr=t.ses_Addr) join v$sql s using (sql_id);

Report message to a moderator

Re: Undo space [message #539923 is a reply to message #539404]

Thu, 19 January 2012 06:42

satish_manukonda
Messages: 1
Registered: January 2012
Location: bangalore

Junior Member

You can use this script ..

SELECT (SUM(UNDO_USED) * Cool

/ 1024 as "space in MB",SID
FROM (select s.username, t.used_ublk UNDO_USED, s.sid sid
FROM v$session s, v$transaction t, v$sort_usage u
WHERE s.taddr = t.addr
group by s.username, t.used_ublk,s.sid)
GROUP BY SID

Report message to a moderator

Re: Undo space [message #539927 is a reply to message #539923]

Thu, 19 January 2012 07:01

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

1/ This does not give you "WHO is eating up most of the undo space and what query he is running"

2/
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Regards
Michel

Report message to a moderator

Previous Topic:	Join with 30 tables
Next Topic:	Problem Index with IN Statement (merged)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 08:11:30 CDT 2024