现象:
insert /*+ append */ into TABLE_NAME select * from TABLE_NAME_2;
出错:ORA-04030: out of process memory when trying to allocate 8192 bytes (sort subheap,sort key)
注:TABLE_NAME 为 nologging,数据量在50万条。
解决:不用append
insert into TABLE_NAME select * from TABLE_NAME_2;
错误消除。
其他一些小表(数据量小),使用insert /*+ append */ into 没有问题。
想请教这是什么原因?
谢谢!
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.
最初由 husthxd 发布
[B]1.数据库版本?平台?
2.是否使用了pga自动管理?
3.是在执行insert /*+ append */ into TABLE_NAME select * from TABLE_NAME_2;的时候出错吗? [/B]
1、
平台:AIX 5.2
数据库版本:
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production
PL/SQL Release 9.2.0.1.0 - Production
CORE
9.2.0.1.0
Production
TNS for IBM/AIX RISC System/6000: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production
2、
SQL> show parameter policy
NAME
TYPE
VALUE
------------------------------------ -------------------------------- ----------
--------------------
workarea_size_policy
string
AUTO
SQL> show parameter pga
NAME
TYPE
VALUE
------------------------------------ -------------------------------- ----------
--------------------
pga_aggregate_target
unknown
1048576000
SQL> show parameter sort
NAME
TYPE
VALUE
------------------------------------ -------------------------------- ----------
--------------------
nls_sort
string
sort_area_retained_size
integer
0
sort_area_size
integer
1024000
3、是的。去掉/*+append */就没事了。数据量也就在40、50万条,目标表上有不少索引。