Solved

how to solve 4031 error?

Posted on 2008-10-10
13
648 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Attention: This article will no longer be maintained. If you have any questions, please feel free to mail me. jgh@FreeBSD.org Please see http://www.freebsd.org/doc/en_US.ISO8859-1/articles/freebsd-update-server/ for the updated article. It is avail…
Every server (virtual or physical) needs a console: and the console can be provided through hardware directly connected, software for remote connections, local connections, through a KVM, etc. This document explains the different types of consol…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

696 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