1.1       现象描述

Oracle分配共享内存失败，ALTER日志显示如下错误信息。

Errors in file /oracle/db/diag/rdbms/ora01/ora01/trace/ora01_reco_233670.trc:

ORA-00604: error occurred at recursive SQL level 1

ORA-04031: unable to allocate 4064 bytes of shared memory ("shared pool","select local_tran_id, global...","sga heap(1,0)","kglsim heap")

Sat Jan 15 09:53:11 2011

DDE: Problem Key 'ORA 4031' was completely flood controlled (0x6)

Further messages for this problem key will be suppressed for up to 10 minutes

Sat Jan 15 10:03:11 2011

DDE: Problem Key 'ORA 4031' was completely flood controlled (0x6)

Further messages for this problem key will be suppressed for up to 10 minutes

Sat Jan 15 10:13:11 2011

DDE: Problem Key 'ORA 4031' was completely flood controlled (0x4)

Further messages for this problem key will be suppressed for up to 10 minutes

说明：

ALTER日志文件目录为“/$ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace/alert__<ORACLE_SID>.log”。

1.2       可能原因

Oracle设置的数据库内存值过少，导致分配内存失败。

1.3       定位思路

检查Oracle内存控制参数配置。

SQL> show parameter memory

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

hi_shared_memory_address             integer     0

memory_max_target                    big integer 1000M

memory_target                        big integer 1000M

shared_memory_address                integer     0

如上所示，Oracle数据库内存配置值过少，只有1GB。

1.4       处理步骤

1. 以oracle用户登录数据库所在机器。
2. 以SYSDBA用户连接数据库。

% sqlplus /