SET LINESIZE 220
SET PAGESIZE 50
COLUMN spid HEADING 'OSpid' FORMAT a10
COLUMN pid HEADING 'Orapid' FORMAT 999999
COLUMN sid HEADING 'Sess id' FORMAT 99999
COLUMN serial# HEADING 'Serial#' FORMAT 999999
COLUMN status HEADING 'Status' FORMAT a12
COLUMN pga_alloc_mem HEADING 'PGA alloc' FORMAT 99,999,999,999
COLUMN pga_used_mem HEADING 'PGA used' FORMAT 99,999,999,999
COLUMN username HEADING 'Oracle user' FORMAT a20
COLUMN osuser HEADING 'OS user' FORMAT a25
COLUMN program HEADING 'Program' FORMAT a40
select * from (SELECT p.spid,
p.pid,
s.sid,
s.serial#,
s.status,
s.type,
p.pga_alloc_mem,
p.pga_used_mem,
s.username,
s.osuser,
s.program
FROM v$process p, v$session s
WHERE s.paddr( + ) = p.addr
ORDER BY p.pga_alloc_mem DESC) where rownum<100 ;