ORA-04031 Shared Memory...

[ajhull]

I understand that the resolution for this problem is to increase the shared pool. However, I am confused why I receive this message on the second database when no activity has been done on it (no users other than myself logged in and I am not doing any parsing etc.)

Could the cause of this be that the database is not being allocated enough memory from the server (never reaching the limit of the shared pool in the pfile)?

I need a reminder - how do I check the memory usage of the shared pool so I can cross reference this with the setting in the pfile and see if it is reaching its limit? What is the unix command?

Thanks in advance.

Adrian
Certified DBA/Developer

 

[dbtoo2001]

cat /etc/system

prompt Shared Pool allocated for use
select value as ALLOCATED_SHARED_MEM from v$parameter where name = 'shared_pool_size';

prompt Shared Pool in use
select sum(sharable_mem) as TOTAL_SHARED_MEM from v$db_object_cache;

prompt Static SQL Memory
select sum(sharable_mem) as STATIC_SQL_MEM from v$sqlarea where executions > 5;

prompt Dynamic SQL Memory
select ( total - static ) as DYNAMIC_SQL_MEMORY
from (select sum(sharable_mem) as total from v$sqlarea) TOTAL_SHARED_MEM,
(select sum(sharable_mem) as static from v$sqlarea where executions > 5) STATIC_SQL_MEM;

prompt per-user per-cursor memory
select sum(250 * users_opening)as PER_CURSOR_MEMORY from v$sqlarea;