If you would to determine the amount of memory database processes are consuming consider the following script

set lines 110
col unm format a30 hea "USERNAME (SID,SERIAL#)"
col pus format 999,990.9 hea "PROC KB|USED"
col pal format 999,990.9 hea "PROC KB|MAX ALLOC"
col pgu format 99,999,990.9 hea "PGA KB|USED"
col pga format 99,999,990.9 hea "PGA KB|ALLOC"
col pgm format 99,999,990.9 hea "PGA KB|MAX MEM"

select s.username||' ('||s.sid||','||s.serial#||')' unm, round((sum(m.used)/1024),1) pus,
round((sum(m.max_allocated)/1024),1) pal, round((sum(p.pga_used_mem)/1024),1) pgu,
round((sum(p.pga_alloc_mem)/1024),1) pga, round((sum(p.pga_max_mem)/1024),1) pgm
from v$process_memory m, v$session s, v$process p
where m.serial# = p.serial# and p.pid = m.pid and p.addr=s.paddr and
s.username is not null group by s.username, s.sid, s.serial# order by unm;

..........................PROC KB....PROC KB........PGA KB........PGA KB........PGA KB
USERNAME (SID,SERIAL#).......USED..MAX ALLOC..........USED.........ALLOC.......MAX MEM
---------------------- ---------- ---------- ------------- ------------- -------------
DBSNMP (120,1810)............64.4....1,882.9.......3,924.7.......6,845.9.......6,845.9
DBSNMP (121,778).............65.0....3,847.2.......6,116.8......11,709.9......15,549.9
DBSNMP (128,4503)............65.2...33,519.5.......9,184.8......14,013.9.....137,405.9
SYS (131,6970)..............111.9...29,879.6.......5,714.8.......8,670.3.....120,798.3
SYSMAN (119,7)...............21.1....1,649.5.......2,933.3.......4,558.4.......4,558.4
SYSMAN (122,11589)..........330.9...15,518.8......16,569.3......24,699.4......66,171.4
SYSMAN (126,17899)...........43.4....3,563.5.......8,667.4......14,781.9......17,597.9
SYSMAN (127,52)..........47,960.6...63,663.5.....176,985.5.....179,086.4.....190,990.4
SYSMAN (129,25990)...........49.7....3,099.9.......8,109.8......12,221.9......12,477.9
SYSMAN (132,10067)...........45.1....6,515.9......13,641.5......23,997.9......27,325.9
SYSMAN (133,6178)............46.7....3,615.5.......8,841.5......14,781.9......15,293.9
SYSMAN (134,13810)...........71.5....2,618.3.......5,555.3......10,429.9......10,429.9
SYSMAN (143,12587)...........47.5....2,670.7.......4,619.8.......9,405.9......10,941.9
SYSMAN (144,1858)...........107.0....2,431.7.......6,834.4......10,685.9......11,197.9
SYSMAN (146,6516)............41.6....2,277.6.......4,171.5.......8,125.9.......8,125.9

If you would to determine the amount of memory database processes are consuming consider the following script

set lines 110
col unm format a30 hea "USERNAME (SID,SERIAL#)"
col pus format 999,990.9 hea "PROC KB|USED"
col pal format 999,990.9 hea "PROC KB|MAX ALLOC"
col pgu format 99,999,990.9 hea "PGA KB|USED"
col pga format 99,999,990.9 hea "PGA KB|ALLOC"
col pgm format 99,999,990.9 hea "PGA KB|MAX MEM"

select s.username||' ('||s.sid||','||s.serial#||')' unm, round((sum(m.used)/1024),1) pus,
round((sum(m.max_allocated)/1024),1) pal, round((sum(p.pga_used_mem)/1024),1) pgu,
round((sum(p.pga_alloc_mem)/1024),1) pga, round((sum(p.pga_max_mem)/1024),1) pgm
from v$process_memory m, v$session s, v$process p
where m.serial# = p.serial# and p.pid = m.pid and p.addr=s.paddr and
s.username is not null group by s.username, s.sid, s.serial# order by unm;

..........................PROC KB....PROC KB........PGA KB........PGA KB........PGA KB
USERNAME (SID,SERIAL#).......USED..MAX ALLOC..........USED.........ALLOC.......MAX MEM
---------------------- ---------- ---------- ------------- ------------- -------------
DBSNMP (120,1810)............64.4....1,882.9.......3,924.7.......6,845.9.......6,845.9
DBSNMP (121,778).............65.0....3,847.2.......6,116.8......11,709.9......15,549.9
DBSNMP (128,4503)............65.2...33,519.5.......9,184.8......14,013.9.....137,405.9
SYS (131,6970)..............111.9...29,879.6.......5,714.8.......8,670.3.....120,798.3
SYSMAN (119,7)...............21.1....1,649.5.......2,933.3.......4,558.4.......4,558.4
SYSMAN (122,11589)..........330.9...15,518.8......16,569.3......24,699.4......66,171.4
SYSMAN (126,17899)...........43.4....3,563.5.......8,667.4......14,781.9......17,597.9
SYSMAN (127,52)..........47,960.6...63,663.5.....176,985.5.....179,086.4.....190,990.4
SYSMAN (129,25990)...........49.7....3,099.9.......8,109.8......12,221.9......12,477.9
SYSMAN (132,10067)...........45.1....6,515.9......13,641.5......23,997.9......27,325.9
SYSMAN (133,6178)............46.7....3,615.5.......8,841.5......14,781.9......15,293.9
SYSMAN (134,13810)...........71.5....2,618.3.......5,555.3......10,429.9......10,429.9
SYSMAN (143,12587)...........47.5....2,670.7.......4,619.8.......9,405.9......10,941.9
SYSMAN (144,1858)...........107.0....2,431.7.......6,834.4......10,685.9......11,197.9
SYSMAN (146,6516)............41.6....2,277.6.......4,171.5.......8,125.9.......8,125.9