I’m listing below some of the queries I have created to see various oracle session related memory statistics. Replace all occurrences of XXXXXXXXXXXXXXXX with your database username. Please drop a comment below if these queries are useful for you guys.

Query for getting ‘session pga memory’ usage for inactive sessions:

select sum(ROUND(s.value/1024)) || ‘ KB’ as pga_memory_usage_inactive from v$sesstat s , v$statname n where s.statistic# = n.statistic# and sid in (select sid from v$session where status=’INACTIVE’ and type= ‘USER’ and username=’XXXXXXXXXXXXXXXX‘) and n.name = ‘session pga memory’ group by name ;

Query for getting ‘session pga memory’ usage for all sessions:

select sum(ROUND(s.value/1024)) || ‘ KB’ as pga_memory_usage_total from v$sesstat s , v$statname n where s.statistic# = n.statistic# and n.name = ‘session pga memory’ group by name ;

Query for getting ‘session uga memory’ usage for inactive sessions:

select sum(ROUND(s.value/1024)) || ‘ KB’ as uga_memory_usage_inactive from v$sesstat s , v$statname n where s.statistic# = n.statistic# and sid in (select sid from v$session where status=’INACTIVE’ and type= ‘USER’ and username=’XXXXXXXXXXXXXXXX‘) and n.name = ‘session uga memory’ group by name ;

Query for getting ‘session uga memory’ usage for all sessions:

select sum(ROUND(s.value/1024)) || ‘ KB’ as uga_memory_usage_total from v$sesstat s , v$statname n where s.statistic# = n.statistic# and n.name = ‘session uga memory’ group by name ;

You can determine how much memory each inactive session is consuming using the following query:

select TO_CHAR(ROUND(s.value/1024),99999999) || ‘ KB’ as Memory_Usage , n.name|| ‘(‘||s.statistic#||’)’ as memory_type , sid from v$sesstat s , v$statname n where s.statistic# = n.statistic# and sid in (select sid from v$session where status=’INACTIVE’ and type= ‘USER’ and username=’XXXXXXXXXXXXXXXX‘) and n.name like ‘%ga memory%’ order by value;

VN:F [1.9.12_1141]
Rating: 9.2/10 (5 votes cast)
VN:F [1.9.12_1141]
Rating: +2 (from 2 votes)
Oracle query to get session pga & uga memory usage for inactive sessions, 9.2 out of 10 based on 5 ratings