Solved

how to solve 4031 error?

Posted on 2008-10-10
13
632 Views
Last Modified: 2013-12-21
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?

 
0
Comment
Question by:daniesh
  • 7
  • 4
13 Comments
 

Author Comment

by:daniesh
ID: 22691203
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
 
LVL 15

Accepted Solution

by:
Shaju Kumbalath earned 500 total points
ID: 22693404
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
 
LVL 34

Expert Comment

by:johnsone
ID: 22693948
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
 

Author Comment

by:daniesh
ID: 22694873
Hi All,
How to check shared pool size used size and free shared pool size.

Thanks,
Daniesh

0
 

Author Comment

by:daniesh
ID: 22694881
Hi
how to check size of physical memory.
0
 

Author Comment

by:daniesh
ID: 22703560
please provide me an update on this?
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:daniesh
ID: 22709638
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
 
LVL 34

Expert Comment

by:johnsone
ID: 22710667
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
 

Author Comment

by:daniesh
ID: 22710970
thanks...
how can we check physical size of memory.
0
 
LVL 34

Expert Comment

by:johnsone
ID: 22711482
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
 

Author Comment

by:daniesh
ID: 22712119
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
 
LVL 34

Expert Comment

by:johnsone
ID: 22712949
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now