Solved

how to solve 4031 error?

Posted on 2008-10-10
13
629 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
FreeBSD on EC2 FreeBSD (https://www.freebsd.org) is a robust Unix-like operating system that has been around for many years. FreeBSD is available on Amazon EC2 through Amazon Machine Images (AMIs) provided by FreeBSD developer and security office…
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 explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

705 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

17 Experts available now in Live!

Get 1:1 Help Now