遭遇一个ORA-04030的奇怪问题

    最近在AIX主机做一个新项目大数据抽取的时候遇到一个奇怪的问题。用远程方式连接数据库后(sqlplus )，当我在执行一个最简单的排序查询语句时出现了ORA-04030的错误，错误信息是：
ORA-04030: out of process memory when trying to allocate 32768 bytes (sort
subheap,sort key)
但是我发现我使用本地连接方式(sqlplus username/password)就不会出现这个错误。ORA-04030一般都是操作系统资源限制问题，最开始调试的时候两种方式都会报错，但调整了系统limit后本地连接方式就没有在出现问题，再次检查了操作系统的资源限制后，并没发现还存在什么问题
time(seconds) unlimited
file(blocks) unlimited
data(kbytes) unlimited
stack(kbytes) 4194304
memory(kbytes) unlimited
coredump(blocks) 2097151
nofiles(descriptors) 2000
我查看两种方式连接的进程情况，如下：
$ ps v 405578
PID TTY STAT TIME PGIN SIZE RSS LIM TSIZ TRS %CPU %MEM COMMAND
405578 - A 0:00 0 4136 23388 xx 46132 19252 0.0 0.0 oraclebj
$ ps v 373362
PID TTY STAT TIME PGIN SIZE RSS LIM TSIZ TRS %CPU %MEM COMMAND
373362 - A 0:01 15 4088 23340 32768 46132 19252 0.1 0.0 oraclebj
我发现LIM不同，第二个进程是远程方式的并存在限制，我怀疑和这个有关系。可资源限制都放开了，为啥会这样。这里我犯了一个经验主义的错误，呵呵。一直认为是主机操作系统的问题，而没有去从数据库上去想，最后跑到METALINK上一查，才发现问题的所在是数据库的监听，呵呵。想想也是，如果是普通的session修改系统limit后也是有重新登录才生效嘛。重启监听后问题解决！

 

oracle相关说明如下：

The information in this article applies to:

Oracle Server - Enterprise Edition - Version: 9.2.0.4
This problem can occur on any platform.

Errors

ORA 4030 "out of process memory when

Symptoms

ORA-4030 While running a SQl from a remote session ,

After making the Changes to cat /etc/security/limits
and setting the value to unlimited for data, stack and memory as below , the query failed from the remote session , but was successfull from the server

========================================
oracle:
fsize = -1
data = -1
stack = -1
memory = -1
========================================

Cause

CT Changed the ulimits but the Listener was not restarted , which led to the problem from the remote session , as listener had the ulimit set to the original value

Fix

After Changing the ulimit make sure that all the processes are restarted i.e listener or other processes which could refer to the session limits of the OS