ORA-04030 (initSubHeap:qk,travElemP:qkspmTravCreate) from SELECT or EXPLAIN PLAN statement (Doc ID 1330496.1)

Last updated on NOVEMBER 28, 2016

Applies to:

Oracle Server - Enterprise Edition - Version 11.2.0.1 and later
Information in this document applies to any platform.

Symptoms

Running a SELECT or EXPLAIN PLAN statement, you consistently get the error :

ORA-04030: out of process memory when trying to allocate 77864 bytes (initSubHeap:qk,travElemP:qkspmTravCreate)



The SELECT statement is same as or similar to :


SELECT OBJECT_NAME, OBJECT_TYPE, 'ALTER ' || OBJECT_TYPE || ' ' || OWNER || '.' ||
 OBJECT_NAME || ' COMPILE' COMANDO
FROM ALL_OBJECTS
WHERE OWNER = 'SCOTT' AND OBJECT_NAME IN
 (SELECT NAME
FROM ALL_DEPENDENCIES
WHERE OWNER LIKE 'SCOTT' AND REFERENCED_NAME IN
 (SELECT DISTINCT (TABLE_NAME)
FROM ALL_TAB_PARTITIONS
 WHERE TABLE_OWNER LIKE 'SCOTT' AND TABLE_NAME IN
 (SELECT DISTINCT NAME
FROM ALL_PART_KEY_COLUMNS C
WHERE OWNER = 'SCOTT' AND C.COLUMN_NAME NOT LIKE 'COD%'))
AND REFERENCED_OWNER = 'SCOTT') AND STATUS = 'INVALID'
AND OBJECT_TYPE IN ('VIEW', 'PROCEDURE', 'FUNCTION', 'PACKAGE')



Setting _optimizer_reuse_cost_annotations to FALSE or _push_join_predicate to FALSE does NOT avoid the ORA-4030


Changes

No changes made to the environment.

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms