ORA-00942,ORA-38818 Error On Creating Materialized View
- Database link is not used.
- Materialized view is created from view and not from table.
- All privileges are in place.
Creation of materialized view as select on a view gives the error,
ORA-00942: table or view does not exist
Creation of materialized view as select on a subquery gives the error,
ORA-22818: subquery expressions not allowed here
Creation of materialized view as select on a customized function gives the error,
ORA-00904: “FUNCTION NAME”: invalid identifier
DDL of the view shows that the view is editioned.
CREATE OR REPLACE FORCE EDITIONABLE VIEW …..
SQL failed because the referenced object could not be found. This error typically occurs when the referenced object is dropped or you do not have permission to reference it.
See the document Doc ID 18536.1 for details:
OERR: ORA-942 “table or view does not exist” Reference Note (Doc ID 18536.1)
Messages in the 10046 trace shows parse error with err=10980.
PARSE ERROR #18446744071504374656:len=26 dep=1 uid=44 oct=3 lid=44 tim=9578336260726 err=10980
select * from <view_name> <<<===
CLOSE #18446744071504374656:c=1,e=1,dep=1,type=0,tim=9578336260882
PARSE #18446744071504444384:c=6,e=6,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=1028580536,tim=9578336261030
BINDS #18446744071504444384:
Bind#0
oacdty=11 mxl=16(16) mxlc=00 mal=00 scl=00 pre=00
oacflg=18 fl2=0001 frm=00 csi=00 siz=16 off=0
kxsbbbfp=ffffffff7d7c49e8 bln=16 avl=16 flg=05
value=00000EB8.0002.0009
EXEC #18446744071504444384:c=118,e=118,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=4,plh=1028580536,tim=9578336261184
FETCH #18446744071504444384:c=6,e=7,p=0,cr=2,cu=0,mis=0,r=1,dep=2,og=4,plh=1028580536,tim=9578336261228
CLOSE #18446744071504444384:c=4,e=4,dep=2,type=3,tim=9578336261256
The name of the view is in uppercase <VIEW_NAME> as per the DDL metadata but the view name specified is in lowercase <view_name> during the mview creation which caused ORA-00942 due to case sensitivity.
After specifying the view name in uppercase, ORA-38818 error is observed.
CREATE MATERIALIZED VIEW <mview_name>
as select * from “<TABLE_NAME>“;
ORA-38818: illegal reference to editioned object APPS.AR_CUSTOMERS
the error is expected behavior due to reference to editioned schema and the materialized view that you create is non editioned.
38818, 00000, “illegal reference to editioned object %s.%s”
// *Cause: An attempt was made to violate the rule “A noneditioned object
// may not depend on an editioned object.”
// *Action: Either make this object editioned; or do not make the illegal
// reference.
“A noneditioned object is a schema object that has a noneditionable type. An edition cannot have its own copy of a noneditioned object. A noneditioned object is identical in, and visible to, all editions.”
“An edition can have its own copy of an editioned object, in which case only the copy is visible to the edition.”
Editionable and Noneditionable Schema Object Types
These schema objects types are editionable:
SYNONYM
VIEW
All PL/SQL object types:
FUNCTION
LIBRARY
PACKAGE and PACKAGE BODY
PROCEDURE
TRIGGER
TYPE and TYPE BODY
All other schema object types are noneditionable. Table is an example of an noneditionable type.
A schema object of an editionable type is editioned if its owner is editions-enabled; otherwise, it is potentially editioned.
A schema object of a noneditionable type is always noneditioned, even if its owner is editions-enabled. A table is an example of an noneditioned object.
Creation of mview from edition enabled view with simple test case:
SQL> create table t as select * from dba_source;
SQL>create force editionable view dv as select * from t;
View created.
SQL>create materialized view mv as select * from edition_test.dv;
create materialized view mv as select * from edition_test.dv
*
ERROR at line 1:
ORA-38818: illegal reference to editioned object EDITION_TEST.DV <=========== expected error
Workaround:
1)Create a new non edition schema similar to the “APPS” schema
2) Create the underlying objects similar to the edition schema “APPS”
3) And create mview using the view