Check Memory Usage For Active User Sessions

This script will help you to figure out memory-heavy sessions actively running.

WITH w_stat_data AS (
    SELECT --+materialize 
       s_stat.sid
    , s_stat.value
    FROM v$sesstat s_stat
    --
    LEFT JOIN v$statname curr_uga
    ON s_stat.statistic# = curr_uga.statistic#
    AND curr_uga.name IN ('session uga memory','session uga memory max')
  )

SELECT ssn.username
, round(curr.value/1024/1024,2) || ' MB' as current_uga_memory
, round(alloc.value/1024/1024,2) || ' MB' as allocated_uga_memory
FROM v$session ssn
--
LEFT JOIN w_stat_data curr
ON curr.sid = ssn.sid
--
LEFT JOIN w_stat_data alloc
ON alloc.sid = ssn.sid
--
WHERE 1=1;