Home » RDBMS Server » Performance Tuning » Undo space
Undo space [message #539402] Sun, 15 January 2012 01:08 Go to next message
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
Re: Undo space [message #539404 is a reply to message #539402] Sun, 15 January 2012 01:24 Go to previous messageGo to next message
John Watson
Messages: 8593
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);



Re: Undo space [message #539923 is a reply to message #539404] Thu, 19 January 2012 06:42 Go to previous messageGo to next message
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
Re: Undo space [message #539927 is a reply to message #539923] Thu, 19 January 2012 07:01 Go to previous message
Michel Cadot
Messages: 67946
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
Previous Topic: Join with 30 tables
Next Topic: Problem Index with IN Statement (merged)
Goto Forum:
  


Current Time: Tue Sep 21 00:43:38 CDT 2021