帮忙查找故障原因？

buwenli

今天数据库宕机，查错误日志报错如下：
ORA-00604: error occurred at recursive SQL level 1
ORA-04030: out of process memory when trying to allocate 2008 bytes (pga heap,frame segment)

查错误代码解释如下：
ORA-04030 out of process memory when trying to allocate string bytes (string,string)

Cause: Operating system process private memory has been exhausted.

Action: See the database administrator or operating system administrator to increase process memory quota. There may be a bug in the application that causes excessive allocations of process memory space.


ORA-00604 error occurred at recursive SQL level string

Cause: An error occurred while processing a recursive SQL statement (a statement applying to internal dictionary tables).

Action: If the situation described in the next error on the stack can be corrected, do so; otherwise contact Oracle Support Services.

请高手指教什么原因出现如上错误

xxdream

初步看是内存耗完。
要仔细分析原因的话，需要知道操作系统及数据库版本。如果是UNIX上，在出现这个错误时，VMSTAT的值也很重要。
另外数据库的参数是怎样设置的啊？

sshd

SQL>; show SGA
SQL>; show parameter pga

post the results (for 9i db)

buwenli

谢谢，我的小机是AIX4.3，ORACLE9.0.1，小型机重新启动后，数据库恢复正常，错误信息是从ALERT_ORC11.LOG中查到的，而且从3月3日开始报的ORA-04030的错误。现在要查找原因应从哪几方面入手，谢谢指教！

cole

哈哈 打补丁吧，是内存泄漏 打到9.2.4 还是9.2.6我忘了，9.2.4之前的版本都有这个bug

buwenli

数据库运行将近两年了，原先没有出现此现象。补丁丛哪里可以得到呀？

buwenli

数据库运行将近两年了，原先没有出现此现象。补丁丛哪里可以得到呀？

ghost

补丁从metalink网站下载

buwenli

是应用的问题造成的内存泄露吗？

sshd

this is not a really bug.
it happened becuase you had SQL operations that needed a lot of PGA memory (you were doing large sorts, for example).
you need to set
workarea_size_policy = auto
pga_aggregate_target = a larger value than what you have now