ORA-04030

最新新装的两套数据库都遇到了ora-04030错误，查看了metalink上的文章，总结了下04030错误分析及解决方法。

Normal 0 7.8 磅 0 2 false false false EN-US ZH-CN X-NONE An ORA-4030 error is an error in the PGA; Memory limitation related to an Oracle rdbms server process is reached.
This includes the database background processes and oracle shadow processes that are spawned directly by the database or the listener.

 

The PGA is comprised of four memory areas:

* User Session Memory

* Private SQL Areas - contains data such as bind information and runtime buffers

* SQL Work Areas

* Cursor and SQL Areas

 

Diagnosing ORA-4030 errors

Is there still sufficient memory available?

# check for the global memory usage on the system like top, vmstat,... And memory management does work differently on each OS

Is_there_an_operating_system_limit_set?

#ulimit -a

Is there an oracle limit set?

The following query can be used to find the total amount of memory allocated to the PGA areas of all sessions:

SQL> select
                sum(value)/1024/1024 Mb
             from
                 v$sesstat s, v$statname n
              where
                  n.STATISTIC# = s.STATISTIC# and
                  name = 'session pga memory';

Which process is requesting too much memory?

You can use the following query to find out oracle's idea of PGA and UGA size for the oracle processes.

SQL> col name format a30

SQL> select

   sid,name,value

from

   v$statname n,v$sesstat s

where

   n.STATISTIC# = s.STATISTIC# and

   name like 'session%memory%'

order by 3 asc;

How to collect information on what the process is actually doing?

You can check in v$sqlarea what is beeing executed with the following query:

SQL> select sql_text  from v$sqlarea a, v$session s where a.address = s.sql_address and s.sid = ;

 

create a heapdump

Set event at instance level to fire for any occurrence of the ORA-4030.

The following will set in memory.

ALTER SYSTEM SET EVENTS '4030 trace name heapdump level 536870917;name errorstack level 3';

To turn off these events:

ALTER SYSTEM SET EVENTS '4030 trace name heapdump off;name errorstack off';

 

Set event only at session level.

This will set event at session level and create trace file when error occurs in that session only.

ALTER SESSION SET EVENTS '4030 trace name heapdump level 536870917;name errorstack level 3';

 

Can attach to a particular process using oradebug to get a heapdump.

This is useful and required if the process grows large, but does not cause an error.

SQL>sqlplus /nolog
SQL>conn / as sysdba
SQL> alter system set max_dump_file_size=unlimited;
SQL> connect sys as sysdba
SQL> oradebug setospid

 

HOW TO RESOLVE THE ORA-4030

1. Reduce the PGA (Program Global Area) for the client process encountering the error. This is valid if the database is not configured with MTS (Multi Threaded Server) or Shared Servers.

At 11g, the view V$MEMORY_RESIZE_OPS will show memory tuning going on within the database between the SGA components and PGA.

2. Increase the amount of memory a UNIX process can request and use from the operating system. This usually refers to stack and/or data size UNIX process resource limits.

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

4. Finally, if you still experience the problem after addressing the above
issues, it's recommended that you move to the latest patchset release to eliminate any possible Oracle product defects.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12366929/viewspace-731901/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12366929/viewspace-731901/