Oracle Script to list the Memory Allocation Per User/Process
The script will provide important information about the Memory Allocation on your database present session.
SET PAGESIZE 9999
COLUMN sid FORMAT 999 HEADING 'SID'
COLUMN oracle_username FORMAT a12 HEADING 'Oracle User' JUSTIFY right
COLUMN os_username FORMAT a9 HEADING 'O/S User' JUSTIFY right
COLUMN session_program FORMAT a18 HEADING 'Session Program' TRUNC
COLUMN session_machine FORMAT a8 HEADING 'Machine' JUSTIFY right TRUNC
COLUMN session_pga_memory FORMAT 9,999,999,999 HEADING 'PGA Memory'
COLUMN session_pga_memory_max FORMAT 9,999,999,999 HEADING 'PGA Memory Max'
COLUMN session_uga_memory FORMAT 9,999,999,999 HEADING 'UGA Memory'
COLUMN session_uga_memory_max FORMAT 9,999,999,999 HEADING 'UGA Memory MAX'
SELECT s.sid sid,
lpad(s.username, 12) oracle_username,
lpad(s.osuser, 9) os_username,
s.program session_program,
lpad(s.machine, 8) session_machine,
(select round(sum(ss.value / 1024 / 1024))
from v$sesstat ss, v$statname sn
where ss.sid = s.sid
and sn.statistic# = ss.statistic#
and sn.name = 'session pga memory') session_pga_memory,
(select round(sum(ss.value / 1024 / 1024))
from v$sesstat ss, v$statname sn
where ss.sid = s.sid
and sn.statistic# = ss.statistic#
and sn.name = 'session pga memory max') session_pga_memory_max,
(select round(sum(ss.value / 1024 / 1024))
from v$sesstat ss, v$statname sn
where ss.sid = s.sid
and sn.statistic# = ss.statistic#
and sn.name = 'session uga memory') session_uga_memory,
(select round(sum(ss.value / 1024 / 1024))
from v$sesstat ss, v$statname sn
where ss.sid = s.sid
and sn.statistic# = ss.statistic#
and sn.name = 'session uga memory max') session_uga_memory_max
FROM v$session s
ORDER BY session_pga_memory DESC
/
