REM
REM DBAToolZ NOTE:
REM	This script was obtained from DBAToolZ.com
REM	It's configured to work with SQL Directory (SQLDIR).
REM	SQLDIR is a utility that allows easy organization and
REM	execution of SQL*Plus scripts using user-friendly menu.
REM	Visit DBAToolZ.com for more details and free SQL scripts.
REM
REM 
REM File:
REM 	s_sga_pga_used.sql
REM
REM <SQLDIR_GRP>USER TRACE MOST</SQLDIR_GRP>
REM 
REM Author:
REM 	Vitaliy Mogilevskiy 
REM	VMOGILEV
REM	(vit100gain@earthlink.net)
REM 
REM Purpose:
REM	<SQLDIR_TXT>
REM	Reports SID's PGA usage
REM	some platforms have a limitation on maximum
REM	PGA you can have (32bit AIX - 256m)
REM	If SID's PGA usage goes above this maximum
REM	session can get disconnected with the following error:
REM	  "ORA-3113: end-of-file on communication channel"
REM	</SQLDIR_TXT>
REM	
REM Usage:
REM	s_sga_pga_used.sql
REM 
REM Example:
REM	s_sga_pga_used.sql
REM
REM
REM History:
REM	08-01-2001	VMOGILEV	Created
REM
REM

ttitle off
btitle off

col username format a15
col name     format a30

select username, terminal, name, value/1024 "PGA Value - KB" 
from v$statname n,
v$session s,
v$sesstat t
where s.sid=t.sid
and n.statistic# = t.statistic#
and s.type = 'USER'
and s.username is not NULL
and n.name = 'session pga memory'
and s.sid=&&sid
--and t.value > &&use_at_least_30000
/