how to solve 4031 error?

Hi Expert,
when i connect to the database for the first time  remotely.

I get the below error.
java.sql.SQLException: ORA-04031: unable to allocate 27672 bytes of shared memory ("shared
pool","unknown object","sga heap(1,0)","session param values")
I just search the solution on google.
it is a recurring issue.
Please help me in solving this issue?

 
danieshAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

danieshAuthor Commented:
Hi Experts,
Please find the information of shared_pool_size
NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------------
hi_shared_memory_address             integer     0
max_shared_servers                   integer     20
shared_memory_address                integer     0
shared_pool_reserved_size            big integer 1258291
shared_pool_size                     big integer 25165824
shared_server_sessions               integer     0
shared_servers                       integer     0
SQL> show parameter cursor

NAME                                 TYPE        VALUE
------------------------------------ ----------- --------------------
cursor_sharing                       string      EXACT
cursor_space_for_time                boolean     FALSE
open_cursors                         integer     500
session_cached_cursors               integer     0
0
Shaju KumbalathDeputy General Manager - ITCommented:
Cause: More shared memory is needed than was allocated in the shared pool.
 Action: If the shared pool is out of memory, either use the dbms_shared_pool package to pin large packages, reduce your use of shared memory, or increase the amount of available shared memory by increasing the value of the INIT.ORA parameters "shared_pool_reserved_size" and "shared_pool_size".

what is the size of physical memory ?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
johnsoneSenior Oracle DBACommented:
In a lot of cases this will help alleviate the problem:

alter system flush shared_pool;

You can also be running into fragmentation of the memory segment.  As far as I know the only fix for fragmentation is to restart the instance.

If you are seeing this frequently, then increasing shared_pool_size would be a good starting point.

Given that the current setting of shared_pool_size appears to be 25M, that is probably your issue.  I would definitely increase that.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

danieshAuthor Commented:
Hi All,
How to check shared pool size used size and free shared pool size.

Thanks,
Daniesh

0
danieshAuthor Commented:
Hi
how to check size of physical memory.
0
danieshAuthor Commented:
please provide me an update on this?
0
danieshAuthor Commented:
Hi Experts,
could you please provide me an update on this?
This post has not been updated since saturday.
i am awaiting.
its urgent.
0
johnsoneSenior Oracle DBACommented:
I use this script a lot.  It help determine who is using the shared pool.  It will total the usage at the end.

set pause off
COLUMN areas                                HEADING Used|Areas
COLUMN sharable         FORMAT 999,999,999  HEADING Shared|Bytes
COLUMN persistent       FORMAT 999,999,999  HEADING Persistent|Bytes
COLUMN runtime          FORMAT 999,999,999  HEADING Runtime|Bytes
COLUMN username         FORMAT A15          HEADING "User"
SET PAGES 59 LINES 80
BREAK ON REPORT
COMPUTE SUM OF sharable ON REPORT
COMPUTE SUM OF persistent ON REPORT
COMPUTE SUM OF runtime ON REPORT
set echo off;
SELECT username,
       SUM (sharable_mem) sharable,
       SUM (persistent_mem) persistent,
       SUM (runtime_mem) runtime,
       COUNT (*) areas
  FROM
     (SELECT username, sharable_mem, persistent_mem, runtime_mem
      FROM v$sqlarea a, dba_users b
      WHERE a.parsing_user_id = b.user_id)
 GROUP BY username
 ORDER BY 2;
0
danieshAuthor Commented:
thanks...
how can we check physical size of memory.
0
johnsoneSenior Oracle DBACommented:
That depends on the operating system.

In most UNIX operating systems, top is installed.  That will tell you a lot about memory pretty quickly.
0
danieshAuthor Commented:
hi,
how much size shioud i increase for shared pool.
SQL> sho sga_max

Total System Global Area  132089808 bytes
Fixed Size                   731088 bytes
Variable Size             100663296 bytes
Database Buffers           25165824 bytes
Redo Buffers                5529600 bytes
SP2-0158: unknown SHOW option "_max"
SQL> sho parameter sga_max

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                         big integer 132089808

thanks.
0
johnsoneSenior Oracle DBACommented:
For now, I would try doubling the shared_pool_size parameter.  I think you should be able to do that with an ALTER SYSTEM while the database is up.  I believe you have enough space to do that.  Be sure that you use scope=both (if using a spfile) or change the value in the init.ora so the change is not lost.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Unix OS

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.