Oracle Database Discussions

Hello,

today I encountered a rather strange error in the context of an mview refresh: one of our development teams did create a rather complex view and wanted to get a corresponding Materialized View. The initial creation worked as expected, but a following refresh failed with an unexpected ORA-00942 error. I was able to reproduce the effect in the following small test case:

-- 12.2.0.1 SE
create table t(
   col1 number
 , col2 varchar2(4000)
);
insert into t(col1, col2) values(1, '[{"type":1,"target":42}]');
insert into t(col1, col2) values(2, '[{"type":1,"target":42},{"type":2,"target":43}]');
create materialized view t_mv
as
select t.col1, t.col2, r.val
  from t,
  LATERAL (SELECT MIN(val) AS val
             FROM JSON_TABLE ( t.col2, '$[*].target'
                               COLUMNS (val NUMBER PATH '$')
                              )
           ) r;
SQL> create materialized view t_mv
  2  as
  3  select t.col1, t.col2, r.val
  4    from t,
  5    LATERAL (SELECT MIN(val) AS val
  6               FROM JSON_TABLE ( t.col2, '$[*].target'
  7                                 COLUMNS (val NUMBER PATH '$')
  8                                )
  9             ) r;
Materialized view created.
SQL> exec dbms_mview.refresh('t_mv')
BEGIN dbms_mview.refresh('t_mv'); END;
*
ERROR at line 1:
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2952
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2370
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 85
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 245
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2352
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 2908
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3191
ORA-06512: at "SYS.DBMS_SNAPSHOT_KKXRCA", line 3221
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 15
ORA-06512: at line 1

I know: the pseudo-JSON column col2 is missing its check constraint - and I would not claim that the use of the lateral join (or the JSON) is actually a good idea, but still the result surprises me: the MView is created and returns the expected result, but a subsequent refresh fails with an ORA-942 - though there is only one table included into the definition. So I created a SQL trace and found there the following information:

kkzfThrowError:  error_pos = kkzfGetNumRows:OCIStmtPrepare2 errcode = 942  msgbuf =ORA-00942: table or view does not exist
kkzfPrintError: error = 31933 btm = 0  emsglen = 137
ORA-31933: error occurred during refresh statistics processing at kkzfGetNumRows:OCIStmtPrepare2
ORA-00942: table or view does not exist
kkzfSetupStatsCtxDrv:1: stats is disabled on error 31933
kkzfSetupStatsCtxDrv:1: error = 31933 is cleared
kkzdQueryObjNumByName:------ORA-1403 data not found ---
kkzdQueryObjNumByName:usrid= 104, oname=VW_LAT_9DCD9C42
kkzdQueryObjNumByName:---------------------------------

So it seems the internally the Oracle tried to gather stats on VW_LAT_9DCD9C42 - which is in my opinion a lateral view as mentioned in https://jonathanlewis.wordpress.com/2011/02/27/internal-views/. I see a similar VT_LAT entry when I create a plan for the query used in the mview definition:

--------------------------------------------------------------------------------------------
| Id  | Operation                | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                 |     2 |  5034 |    60   (0)| 00:00:01 |
|   1 |  NESTED LOOPS            |                 |     2 |  5034 |    60   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL      | T               |     2 |  4030 |     2   (0)| 00:00:01 |
|   3 |   VIEW                   | VW_LAT_A18161FF |     1 |   502 |    29   (0)| 00:00:01 |
|   4 |    SORT AGGREGATE        |                 |     1 |     2 |            |          |
|   5 |     JSONTABLE EVALUATION |                 |       |       |            |          |
--------------------------------------------------------------------------------------------

So my question is: has anyone seen a similar behaviour? I could not find much about ORA-31933 in MOS (and ORA-942 was also not extremly helpful for different reasons). And could you think of a way to avoid the internal statistics gathering to get the mview refresh working? Ok, actually two questions...

It not hard to find a workaround for the problem (since the object is only refreshed on demand, and it should be no problem to create an intermediate table with CTAS and base the mview refresh on this table instead of using the query), but I would be happy to avoid additional complexity.

Regards

Martin Preiss