上一篇主要说明了问题的产生原因及其解决方法，下面进行这个错误的重现：
[oracle@mydb1 ~]$ ulimit -a
core file size (blocks, -c) unlimited
data seg size (kbytes, -d) unlimited
file size (blocks, -f) unlimited
max memory size (kbytes, -m) unlimited
open files (-n) unlimited
pipe size (512 bytes, -p) 64
stack size (kbytes, -s) unlimited
cpu time (seconds, -t) unlimited
max user processes (-u) 4096
virtual memory (kbytes, -v) unlimited
[oracle@mydb1 ~]$ sqlplus yansp/yansp
SQL*Plus: Release 10.2.0.4.0 - Production on Wed Mar 21 22:05:21 2012
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> ALTER INDEX PK_SBINFO REBUILD;
Index altered.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
当前的data seg size 为 unlimited (无限制)。
[oracle@mydb1 ~]$ ulimit -d 32768
[oracle@mydb1 ~]$ ulimit -a
core file size (blocks, -c) unlimited
data seg size (kbytes, -d) 32768
file size (blocks, -f) unlimited
max memory size (kbytes, -m) unlimited
open files (-n) unlimited
pipe size (512 bytes, -p) 64
stack size (kbytes, -s) unlimited
cpu time (seconds, -t) unlimited
max user processes (-u) 4096
virtual memory (kbytes, -v) unlimited
[oracle@mydb1 ~]$ sqlplus yansp/yansp
SQL*Plus: Release 10.2.0.4.0 - Production on Wed Mar 21 22:09:52 2012
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> ALTER INDEX PK_SBINFO REBUILD;
ALTER INDEX PK_SBINFO REBUILD
*
ERROR at line 1:
ORA-04030: out of process memory when trying to allocate 64544 bytes (sort
subheap,sort key)
SQL>
修改单个进程的数据段限制为32768，此时重建索引报错。
C:\Documents and Settings\shoupeng.yan>sqlplus
SQL*Plus: Release 10.2.0.1.0 - Production on 星期三 3月 21 22:10:39 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> ALTER INDEX PK_SBINFO REBUILD;
索引已更改。
SQL>
但是此时通过Windows客户端的SQLPLUS连接数据库不会报错，因为此时的监听器还是用的原来的ulimits配置。
[oracle@mydb1 ~]$ ulimit -a
core file size (blocks, -c) unlimited
data seg size (kbytes, -d) 32768
file size (blocks, -f) unlimited
max memory size (kbytes, -m) unlimited
open files (-n) unlimited
pipe size (512 bytes, -p) 64
stack size (kbytes, -s) unlimited
cpu time (seconds, -t) unlimited
max user processes (-u) 4096
virtual memory (kbytes, -v) unlimited
[oracle@mydb1 ~]$ lsnrctl stop
LSNRCTL for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Production on 21-MAR-2012 22:11:17
Copyright (c) 1991, 2007, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
The command completed successfully
[oracle@mydb1 ~]$ lsnrctl start
LSNRCTL for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Production on 21-MAR-2012 22:11:25
Copyright (c) 1991, 2007, Oracle. All rights reserved.
Starting /u01/oracle/product/db10gr2/bin/tnslsnr: please wait...
TNSLSNR for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Production
System parameter file is /u01/oracle/product/db10gr2/network/admin/listener.ora
Log messages written to /u01/oracle/product/db10gr2/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=mydb1)(PORT=1521)))
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Production
Start Date 21-MAR-2012 22:11:25
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/oracle/product/db10gr2/network/admin/listener.ora
Listener Log File /u01/oracle/product/db10gr2/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=mydb1)(PORT=1521)))
The listener supports no services
The command completed successfully
[oracle@mydb1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Wed Mar 21 22:11:32 2012
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> alter system register;
System altered.
SQL>
重新启动监听器，使监听器继承当前的ulimits配置。
然后通过Windows客户端的sqlplus连接数据库，就会出现ORA-04030错误。
C:\Documents and Settings\shoupeng.yan>sqlplus
SQL*Plus: Release 10.2.0.1.0 - Production on 星期三 3月 21 22:11:41 2012
Copyright (c) 1982, 2005, Oracle. All rights reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> ALTER INDEX PK_SBINFO REBUILD;
ALTER INDEX PK_SBINFO REBUILD
*
第 1 行出现错误:
ORA-04030: 在尝试分配 64544 字节 (sort subheap,sort key) 时进程内存不足
而在数据库上新开一个shell ，不限制单进程的data seg size,执行索引重建不会报错：
[oracle@mydb1 ~]$ ulimit -a
core file size (blocks, -c) unlimited
data seg size (kbytes, -d) unlimited
file size (blocks, -f) unlimited
max memory size (kbytes, -m) unlimited
open files (-n) unlimited
pipe size (512 bytes, -p) 64
stack size (kbytes, -s) unlimited
cpu time (seconds, -t) unlimited
max user processes (-u) 4096
virtual memory (kbytes, -v) unlimited
[oracle@mydb1 ~]$ sqlplus yansp/yansp
SQL*Plus: Release 10.2.0.4.0 - Production on Wed Mar 21 22:15:02 2012
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> ALTER INDEX PK_SBINFO REBUILD;
Index altered.
SQL>
