SET LINESIZE 120
SET PAGESIZE 120
COLUMN SPID HEADING 'OSPID' FORMAT A8
COLUMN PID HEADING 'ORAPID' FORMAT 999999
COLUMN SID HEADING 'SESS ID' FORMAT 99999
COLUMN SERIAL# HEADING 'SERIAL#' FORMAT 999999
COLUMN STATUS HEADING 'STATUS' FORMAT A8
COLUMN PGA_ALLOC_MEM HEADING 'PGA ALLOC'
COLUMN USERNAME HEADING 'ORACLEUSER' FORMAT A12
COLUMN OSUSER HEADING 'OS USER' FORMAT A12
COLUMN PROGRAM HEADING 'PROGRAM' FORMAT A28

SELECT
P.SPID,
P.PID,
S.SID,
S.SERIAL#,
S.STATUS,
P.PGA_ALLOC_MEM,
S.USERNAME,
S.OSUSER,
S.PROGRAM
FROM V$PROCESS P, V$SESSION S
WHERE S.PADDR(+) = P.ADDR
AND S.SID=&SESSION_ID
ORDER BY P.PGA_ALLOC_MEM DESC;