ITPub博客

首页 > Linux操作系统 > Linux操作系统 > ORA-4031 in Large Pool Executing DBMS_METADATA.GET_DDL By Shared Server Conn

ORA-4031 in Large Pool Executing DBMS_METADATA.GET_DDL By Shared Server Conn

原创 Linux操作系统 作者:spider0283 时间:2012-04-05 20:46:29 0 删除 编辑
ID 1271785.1

In this Document
  Symptoms
  Cause
  Solution
  References


Applies to:

Oracle Server - Enterprise Edition - Version: 11.1.0.6 to 11.2.0.3 - Release: 11.1 to 11.2
Information in this document applies to any platform.

Symptoms

  • ORA-4031 / ORA-600 [729] occurs when executing dbms_metadata.get_ddl package.
SELECT dbms_metadata.get_ddl('TABLE', 'TEST') FROM dual;
  • This only occurs when connected via Shared Server connectivity. Issue does not reproduce with Dedicated Server connectivity.
  • The ORA-4031 error will typically be for a request of allocation "kuxLpxAlloc" 
  • The memory leak will also show as allocation "kuxLpxAlloc"


TEST CASE
=========
-- Create a test user
create user tc identified by tc default tablespace users;
grant dba to tc;

-- Connect to a shared server
connect tc/tc@XXX -- Replace XXX by a valid shared server service name.

-- Verify Session connectivity
select a.sid, a.serial#, a.server, b.spid
from v$session a, v$process b
where a.paddr=b.addr
and sid in (select sid from v$mystat where rownum=1);

  SID    SERIAL#    SERVER SPID
----- ---------- --------- -----------
   27         10    SHARED 20442    <== Shared Server


-- Create test table
create table TEST (col1 varchar2(20));

-- Reproduce the error
SELECT dbms_metadata.get_ddl('TABLE', 'TEST') FROM dual;

ORA-04031: unable to allocate 4194352 bytes of shared memory ("large
pool","unknown object","session heap","kuxLpxAlloc")
ORA-06512: at "SYS.UTL_XML", line 330
ORA-06512: at "SYS.DBMS_METADATA_INT", line 10569
ORA-06512: at "SYS.DBMS_METADATA_INT", line 10684
ORA-06512: at "SYS.DBMS_METADATA", line 3001
ORA-06512: at "SYS.DBMS_METADATA", line 3897
ORA-06512: at "SYS.DBMS_METADATA", line 5678
ORA-06512: at line 1



TRACE FILE
The resultant trace file confirms the following functions on the call stack:

CALL STACK
----------
Error: ORA-4031 [4194352] [large pool] [unknown object] [session heap]
[kuxLpxAlloc] [] [] [] [] [] [] []
...
[04]: kghnospc [KGH]<-- Signaling
[05]: kghalf [KGH]
[06]: LpxMemAlloc []
[07]: lpxlpaxdescendant []
[08]: lpxxpgetstrval []
[09]: lpxevalcomparenset []
[10]: lpxevalexprcont []
[11]: lpxevalexpr []
[12]: LpxsCondTestCondition []
[13]: LpxsCondProcessAction []
...


Error: ORA-600 [729] [69984] [space leak] [] [] [] [] [] [] [] [] []
...
[02]: ksmuhe [KSM]<-- Signaling
[03]: ksmugf [KSM]
[04]: ksuxds [ksu]
[05]: ksudel [ksu]
[06]: opilof []
[07]: opiodr []
...


Cause

This issue is addressed in Bug:9906177 which is closed as a duplicate of unpublished Bug 6370852.

The issue cannot be fixed due to architectural reasons and plan is to replace internal LPX related functions with DBMS_XMLDOM and other related packages in future release.

Solution

A fix for this issue is not possible due to architectural restrictions.

Use a workaround to avoid the ORA-4031/memory leak.

Avoid using shared server connectivity to execute dbms_metadata.get_ddl procedure.

OR

If shared server connection is required, increase size of large pool to handle the higher memory requirements.


References

BUG:9906177 - ORA-04031 FROM SHARED SERVER WHEN EXECUTING DBMS_METADATA.GET_DDL

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/38267/viewspace-720408/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-03-29

  • 博文量
    194
  • 访问量
    608353