My Oracle Support Banner

ORA-4031 Raised When Parsing CTAS Although Select Statement Completes Without Error (Doc ID 2424650.1)

Last updated on FEBRUARY 20, 2019

Applies to:

Oracle Database - Standard Edition - Version 12.1.0.2 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Cloud Service - Version N/A and later
Information in this document applies to any platform.

Symptoms

  08:18:29 SQL> set time on
  08:18:29 SQL> set timing on
  08:18:29 SQL> CREATE TABLE BRAND_MST_TMP_Z_TEST
  08:18:29   2  AS
  08:18:29   3  SELECT *
  08:18:29   4  FROM BRAND_MST_V;
  FROM BRAND_MST_V
       *
  ERROR at line 4:
  ORA-00604: error occurred at recursive SQL level 1
  ORA-04031: unable to allocate 40 bytes of shared memory
  ("shared pool","unknown object","SQLA","tmp")
  Elapsed: 01:51:24.87

  00:25:17 SQL> set timing on
  00:25:17 SQL> SELECT *
  00:25:17   2  FROM BRAND_MST_V;
  no rows selected
  Elapsed: 00:03:34.65

 

5 LARGEST SUB HEAPS for heap name="sga heap(1,0)"   desc=0x60061600
    Subheap ds=0xa797b4e0  heap name=   SQLA^6aadef2d  size= 871729936 *<<<<<<<<----
     owner=0xa797b398  latch=(nil)
    Subheap ds=0x86b2f558  heap name=  XDBSC^a83ba4e6  size=   6766136
     owner=0x86b2f508  latch=(nil)
    Subheap ds=0x6000f170  heap name=  KSFD SGA I/O b  size=   4190408
     owner=(nil)  latch=(nil)
    Subheap ds=0x87d07dc8  heap name=  XDBSC^acdb9710  size=   3644144
     owner=0x87d07d18  latch=(nil)
    Subheap ds=0xa914d358  heap name=    KUPP subheap  size=    930120
     owner=(nil)  latch=(nil)

 0x6aadef2d is the problematic CTAS statement.

SQL>select sql_text from v$sql
      where hash_value =
     utl_raw.cast_to_binary_integer(hextoraw('6aadef2d'));
 
  SQL_TEXT
  ---------------------------------------------------------------
  CREATE TABLE BRAND_MST_TMP_Z_TEST AS SELECT * FROM BRAND_MST_V
kghfnd_in_free_lists <- kghprmalo <- kghalp <- qcopCreateStr <- qkebCreateConstantOpn
<- kkeStoreActualVal_Int <- kkestRCHistgrm <- kkercs <- kkehrd <- kkeapr
<- kkogbyi <- kkoiqb <- kkooqb <- kkoqbc <- apakkoqb <- apaqbdDescendents
<- apaqbdDescendents <- apaqbd <- kkqctFullCostTransfQB <- kkqctCostTransfQB
<- kkqctdrvSU <- kkqutruns <- kkqutruns <- kkqutruns <- kkqutruns
<- kkqutruns <- kkqutruns <- kkqutruns <- kkqutruns <- kkqutruns
<- kkqutruns <- kkqutruns <- kkqutruns <- kkqutruns <- kkqutruns
<- kkqutruns <- kkqutruns <- kkqutruns <- kkqutruns <- kkqutruns
<- kkqutruns <- kkqutruns <- kkqutruns <- kkqutruns <- kkqutruns
<- kkqutruns <- kkqutruns <- kkqutruns <- kkqutruns <- kkqutruns
<- kkqutruns <- kkqutruns <- kkqutruns <- kkqutruns <- kkqutruns
<- kkqutruns <- kkqutruns <- kkqutruns <- kkqutruns <- kkqutruns
<- kkqutruns <- kkqutruns <- kkqutruns <- kkqutruns <- kkqutruns
<- kkqutruns <- kkqutruns <- kkqutruns <- kkqutruns <- kkqutruns
<- kkqudrv <- kkqctdrvTD <- kkqdrv <- kkqctdrvIT <- apadrv
<- opitca <- kksLoadChild <- kxsGetRuntimeLock <- kksfbc <- kkspsc0
<- kksParseCursor <- opiosq0 <- kpoal8 <- opiodr <- ttcpip
<- opitsk <- opiino <- opiodr <- opidrv <- sou2o
<- opimai_real <- ssthrdmain <- main

  

Changes

 

Cause

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Changes
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.