Find out SGA PGA Size

SET LINESIZE 145
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 ss.value 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 ss.value 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 ss.value 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 ss.value 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
/

SID  Oracle User  O/S User Session Program     Machine     PGA Memory PGA Memory Max     UGA Memory UGA Memory MAX
—- ———— ——— —————— ——– ————– ————– ————– ————–
14          SYS    oracle sqlplus@bmau32.uk. bmau32.u     14,040,528     14,040,528        209,312        209,312
6                 oracle oracle@bmau32.uk.p bmau32.u     10,670,592     10,670,592         78,496         78,496
5                 oracle oracle@bmau32.uk.p bmau32.u     10,378,816     10,378,816         78,496         78,496
31          SYS                                            5,504,568     26,138,992      4,856,512     24,737,312
13                 oracle oracle@bmau32.uk.p bmau32.u      4,660,064      4,660,064         78,496         78,496
12                 oracle oracle@bmau32.uk.p bmau32.u      4,618,136      4,618,136         78,496         78,496