Viewing Memory Use for Each User Session

Viewing Memory Use for Each User Session
The following query lists all current sessions, showing the Oracle user and current UGA (user global area) memory use for each session:

SELECT USERNAME, VALUE || 'bytes' "Current UGA memory"
FROM V$SESSION sess, V$SESSTAT stat, V$STATNAME name
WHERE sess.SID = stat.SID
AND stat.STATISTIC# = name.STATISTIC#
AND name.NAME = 'session uga memory';

USERNAME Current UGA memory
------------------------------ ---------------------------------------------
18636bytes
17464bytes
19180bytes
18364bytes
39384bytes
35292bytes
17696bytes
15868bytes
USERSCOTT 42244bytes
SYS 98196bytes
SYSTEM 30648bytes

11 rows selected.


To see the maximum UGA memory ever allocated to each session since the instance started,
replace 'session uga memory' in the preceding query with 'session uga memory max'.