ORA-604, ORA-1405 After Truncating A Partitioned Table (Doc ID 945737.1)

Last updated on MARCH 27, 2014

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.4 to 11.2.0.2 [Release 10.2 to 11.2]
Information in this document applies to any platform.

Symptoms

  1. After a partitioned table is truncated, a query based on that table fails with ORA-604 and/or ORA-1405.
  2. There is a materialized view on the partitioned table.  
  3. Dropping and recreating the materialized view resolves the issue until the next truncation of the partitioned table.  
  4. Disabling query rewrite on the materialized view avoids the error.
  5. The tracefile will contain the following (you may need to set event 604 in order to get a tracefile):
    -- to get tracefile if one is not generated:
    alter session set max_dump_file_size = unlimited;
    alter session set events '604 trace name errorstack forever, level 3');

    ksedmp: internal or fatal error
    ORA-604: error occurred at recursive SQL level 1
    ORA-10980: prevent sharing of parsed query during Materialized View query 
    generation
    Current SQL statement for this session:
    BEGIN
       DBMS_MVIEW.REFRESH('OWNER.MVIEW_NAME');
    END;
    ----- PL/SQL Call Stack -----
      object      line  object
      handle    number  name
    7000000298aaf30        81  package body SYS.DBMS_SNAPSHOT
    . . .

  6.  The call stack will resemble one of the following:
    ----------Call stack----------
    ksesec1 <- rpidrr <- kprball <- kprbprsu <- kprbprs <- kkzparse <- 
    kkzfrsh_execute <- kkzfrsh <- kkzifr3g <- spefcmpa <- spefmccallstd 
    <- peftrusted <- psdexsp <- rpiswu2 <- psdextp . . .

----------Call stack----------
kgesev <- ksesec1 <- rpidrr <- kprball <- kkqsgpmop <- kkqsceut <- kkqsckfpc <- kkqscdrv <- kkqsechk2 <- kkqsechk <- kkqspsum <- kkqsedrv <- kkqseqb <- kkqsrqb <- kkqsRewriteCurrentQ <- kkqscqb <- kkqsptq <- kkqsptqur <- $cold_kkqsrdrv <- kkqdrv <- opiSem <- opiprs <- kksParseChildCursor . . .

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