Thursday, 31 October 2013

ORA-04031: unable to allocate 4120 bytes of shared memory ("shared pool"


ORA-00604: error occurred at recursive SQL level 3
ORA-04031: unable to allocate 4120 bytes of shared memory ("shared pool","select name,password,datats#...","Typecheck","kgghteInit")


Issue is shared_pool_reserved_size had a wrong value. It should be 5-10% from the shared_pool size            

----------------------------------------

SQL> show parameter sga

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 6G
sga_target                           big integer 0
SQL> show parameter shared

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address             integer     0
max_shared_servers                   integer
shared_memory_address                integer     0
shared_pool_reserved_size            big integer 5033164
shared_pool_size                     big integer 96M
shared_server_sessions               integer
shared_servers                       integer     1
SQL>


Issue resolved by changing the shared_pool_reserved_size and shared_pool_size

SQL> alter system set sga_target=6G scope=spfile;
SQL> alter system set shared_pool_size=0 scope=spfile;
SQL> alter system set shared_pool_reserved_size=6m scope=spfile;



1 comment:

  1. Regards
    Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
    LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
    Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at training@oracleappstechnical.com | +91 - 9581017828.

    ReplyDelete

Number of Visitors