Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

how to solve 4031 error?

Posted on 2008-10-10
13
Medium Priority
?
668 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 2000 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 35

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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
 

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 35

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 35

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 35

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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

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…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Suggested Courses

926 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