--

-- List memory allocations RAC Sessions.

--

SET PAUSE ON

SET PAUSE 'Press Return to Continue'

SET PAGESIZE 60

SET LINESIZE 300

COLUMN inst_id FORMAT 9

COLUMN username FORMAT A20

COLUMN module FORMAT A40

COLUMN program FORMAT A40

SELECT a.inst_id,

       NVL(a.username,'(oracle)') AS username,

       a.module,

       a.program,

       Trunc(b.value/1024) AS memory_kb

FROM   gv$session a,

       gv$sesstat b,

       gv$statname c

WHERE  a.sid = b.sid

AND    a.inst_id = b.inst_id

AND    b.statistic# = c.statistic#

AND    b.inst_id = c.inst_id

AND    c.name = 'session pga memory'

AND    a.program IS NOT NULL

ORDER BY b.value DESC

/