Hi Tom,

I would like to know which queries currently running is consuming host memory

From OS level i came to know that the below process is consuming memory -

$ ps aux | head -1; ps aux | sort -rn +3 | head

USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
oracle 14734 0.0 14.2 7362260 4698604 ? Ss Nov15 0:58 oracleABHMOKA01 (LOCAL=NO)

From SQL plus i came to know the user -

SQL> SELECT s.sid, s.serial#, p.spid, s.username, s.program FROM v$session s JOIN v$process p ON p.addr = s.paddr and SPID = '&spid';
Enter value for spid: 14734

SID SERIAL# SPID USERNAME PROGRAM
---------- ---------- ------------ ------------------------------ ------------------------------------------------
667 11506 14734 PHARMACY w3wp.exe

Now i would like to know -

1. which queries are currently running by the above user

2. which of them is consuming memory

Thanks
Abdul

select
  st.sid,
  s.name, 
  st.value
from v$statname s, v$sesstat st
where st.STATISTIC# = s.STATISTIC#
and s.name like '%pga%'