Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

getting error -04030

Posted on 2011-10-18
4
Medium Priority
?
1,342 Views
Last Modified: 2013-01-16
Hi Guys,
I am trying to execute a stored procedure and I am getting error-04030 out of memory, My maximum pga allocated is 3 gb. Can i increase the size of PGA if Yes, whats the right procedure.
 ulimit -a
time(seconds)        unlimited
file(blocks)         unlimited
data(kbytes)         unlimited
stack(kbytes)        32768
memory(kbytes)       unlimited
coredump(blocks)     unlimited
nofiles(descriptors) unlimited
threads(per process) unlimited
processes(per user)  unlimited


trace file
Errors in file /s01/diag/rdbms/diw/diw/trace/diw_ora_5374004.trc  (incident=9865):
ORA-04030: out of process memory when trying to allocate 4194344 bytes (QERHJ hash-joi,QERHJ list array)
Incident details in: /s01/diag/rdbms/diw/diw/incident/incdir_9865/diw_ora_5374004_i9865.trc
Errors in file /s01/diag/rdbms/diw/diw/trace/diw_ora_5374004.trc  (incident=9866):
ORA-04030: out of process memory when trying to allocate 560248 bytes (pga heap,kco buffer)
ORA-04030: out of process memory when trying to allocate 4194344 bytes (QERHJ hash-joi,QERHJ list array)
Incident details in: /s01/diag/rdbms/diw/diw/incident/incdir_9866/diw_ora_5374004_i9866.trc
Errors in file /s01/diag/rdbms/diw/diw/incident/incdir_9865/diw_ora_5374004_i9865.trc:
ORA-04030: out of process memory when trying to allocate 560248 bytes (pga heap,kco buffer)
0
Comment
Question by:nirvairghuman
2 Comments
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 252 total points
ID: 36987245
The docs tell you how to increase the PGA:

7.5.1 Configuring Automatic PGA Memory

http://download.oracle.com/docs/cd/E11882_01/server.112/e16638/memory.htm#PFGRF01401

The exact steps depend on how you set up your instance with the other memory parameters.

Now for the other question:  can you increase the size?

We have no way to anser this.  If you have available memory there should be no reason you cannot.  It depends on your configuration and available resources.
0
 
LVL 35

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 248 total points
ID: 36987875
An Oracle database is extremely tunable.  There are lots of settings you can change.  Which settings should you consider changing and what values should they be changed to?  That's where things get interesting!  Large books have been written on this subject, and there is no single answer that fits all servers, all applications and all organizations.

There are many factors to be included here that we don't know.  These include:
1. How much RAM does the server have?
2. How many CPU sockets and CPU cores per socket does the server have?
3. Is this server dedicated to running only one instance of Oracle?
4. Is this a 32-bit or 64-bit server?
5. Which Oracle version is this?
6. What kind of application is this (OLTP, Data Warehouse, hybrid, etc.)?
7. Is this a packaged application?  If yes, which one?
8. How many concurrent user sessions are expected?
9. What kind of disk system does the server use (local SCSI or SATA, NAS, SAN, a combination, etc.)?
10. What is the host O/S?
0

Featured Post

Independent Software Vendors: 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!

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

810 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