How to resolve ORA-4030 errors on UNIX

Reasons of ORA-04030
When a client program connects to oracle database an oracle process does work on behalf of the client. The Oracle process allocated memory from PGA(Program Global Area). For dedicated server process PGA contains stack, user session data, cursor information and the sort area. For shared server configuration UGA(user session data, cursor information and the sort area) is allocated in the SGA (System Global Area) and will not be responsible for ORA-4030 errors.

The ORA-4030 can come based on various reasons which is noted below.
1)The Oracle process need more memory in order to request client program and it requests additional memory from Operating System but the Operating System can't serve the request. This is likely to happen if OS does not have enough memory or swap space is not available.

2)There is a memory limit restriction from OS for oracle process to use. Oracle process already use the limit and now it requested additional memory and hence error appears.

3)Might be an oracle bug.

Solution of The Problem
1)Reduce the PGA(Program Global Area): If you don't have enough memory on your system then reducing the PGA can help you to avoid the error. In that case oracle process will not request more memory from OS and if needed more space it will use temporary tablespace segment. But lower the PGA can impact on database performance. If you don't have PGA_AGGREGATE_TARGET set then in order to set lower PGA set SORT_AREA_SIZE parameter to a lower value. You can set it dynamically by,
ALTER SYSTEM SET SORT_AREA_SIZE=10M;

If you have set PGA_AGGREGATE_TARGET then to reduce PGA you have to set PGA_AGGREGATE_TARGET toa lower value.
ALTER SYSTEM SET PGA_AGGREGATE_TARGET=100M;

2)If you did not enabled automatic PGA management then enable the automatic SQL execution memory management feature by setting the parameter WORKAREA_SIZE_POLICY to AUTO and by specifying a size of PGA_AGGREGATE_TARGET then it will lessen to happen ORA-4030 error. As by setting automatic SQL execution memory management feature SORT_AREA_SIZE, HASH_AREA_SIZE are handled by oracle automatically and allocate/deallocate by demand.

ALTER SYSTEM SET WORKAREA_SIZE_POLICY=AUTO;

3)If you impose limit to oracle to use memory then increase the amount of memory a UNIX process can request and use from the operating system. The system administrator can see this soft limit
by issuing,
$limit
In order to see hard limit issue,
$limit -h

If you want to increase a specific resource soft limit by specifying it's name and the new value then on
C shell
use,
csh> limit -h datasize 524289
csh> limit -h datasize
To make all resource unlimited issue,
csh>unlimit
If you use
Bash and Korne Shell
then use,
$ulimit -a
In order to set all resource to unlimited.

4)Increase the amount of swap space available on the system. You should have 2-3 times the amount of physical memory available as swap space.

5)Increase the physical RAM on your system if you have lower RAM. If you don't have the scope to increase your RAM then lower the SGA settings. Lowering SGA size will allow to use more PGA.

6)If some PL/SQL procedures need much memory then you might think to rewrite it so that it needs less memory.

7)Install the latest patch set.

Comments

Post a Comment

Popular posts from this blog

ORA-04062: timestamp of procedure has been changed

Problem Description In the database I have created one procedure named a as below. create or replace procedure a(a number) as begin insert into t1 values(1); commit; end; / Now after creating database link using remote database machine whenever I access this procedure "A" it executes successfully and I get value "1" in table t1. Like below in example where orastdby_m is the database link, shaik is the schema name and value 1 is the argument value though argument value is not used in the procedure. SQL> exec shaik.a@orastdby_m(1); PL/SQL procedure successfully completed. Now in the source database machine I changed the procedure as below. Though you can change anything like any literal; adding space or remove space. I changed value to be inserted from 1 to 2. create or replace procedure a(a number) as begin insert into t1 values(2); commit; end; / Now in the other database whenever I execute the procedure using database link it throws error ORA-04062. But subseq...
Read more

ORA-00939: too many arguments -when case arguments exceed 255

Problem Description In all Oracle 10.1g and oracle 10.2.0.1, 10.2.0.2, 10.2.0.3 whenever there is more than 255 arguments inside CASE statements then following error is returned. ORA-00939: too many arguments for function But surprisingly on Oracle 9.2 database the same code does not return any error. Cause of the Problem As we see the same code return error in 10g but it is well on 9.2. In fact this is problem of oracle 9.2 that it does not return any error. It should return error if there is more than 255 arguments inside CASE statements. In oracle 9.2 this is bug number 2909927 and this bug is fixed in oracle 10.1 and 10.2. That's why in 10.2 and 10.1 error returns if arguments exceed 255. Up to Oracle 10.2.0.3 and earlier version, the arguments of CASE statement is limit to 255 and it does not support exceed over 255 and hence error return if it exceed. However in Oracle 10.2.0.4 and later the arguments limit of CASE statement has increased to 32767. Solution of the Problem Opt...
Read more

ORA-31655: no data or metadata objects selected for job

Problem Description You are going to do data pump export operation in order to export objects based on filtering via EXCLUDE or INCLUDE parameter of expdp. In this example you wanted tables 'ACCOUNT_GROUP','ADDRESS','AREA_GROUP' and all the tables starting with word CV(like 'CV', 'CV_EXPERIENCE', 'CV_EDUCATION' etc) Your parameter file is like below. userid=smilebd/a directory=d dumpfile=b.dmp include =TABLE:"IN('ACCOUNT_GROUP','ADDRESS','AREA_GROUP')" include =TABLE:"LIKE 'CV%' " And you invoke expdp as expdp parfile=d:\parfile.txt from command line. But it fails with below message on my windows PC. C:\>expdp parfile=d:\parfile.txt Export: Release 10.1.0.4.2 - Production on Thursday, 29 January, 2009 14:53 Copyright (c) 2003, Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Produc tion With the Partitioning, OLAP and Data Mining ...
Read more