CAN WE CREATE A MATERIALIZED VIEW FROM VIEW ? ORA-00942: table or view does not exist
Answer is “Yes” we can create MVIEW from VIEW
But oracle always look for syntax and semantics with case sensitive match.
If special characters are used while creating a view, then the same characters need to specified while creating MVIEW as well. Otherwise, it end up with ORA-00942 errors which confuse to go in wrong direction of focusing on privileges.
kIsH@Xhydra<>create table ds as select * from dba_source;
Table created.
kIsH@Xhydra<>create or replace force editionable view "TEST"."DSV" as select * from ds;
View created.
kIsH@Xhydra<>create materialized view dsmv2 as select * from "dsv";
create materialized view dsmv2 as select * from "dsv"
*
ERROR at line 1:
ORA-00942: table or view does not exist
Since all privileges are there in place for creating MVIEW, the metadata of the view can be pulled to check the original syntax used while creation of view.
kIsH@Xhydra<>create materialized view dsmv2 as select * from "DSV";
Materialized view created.
kIsH@Xhydra<>create or replace force editionable view "TEST"."dsv" as select * from ds;
View created.
kIsH@Xhydra<>select dbms_metadata.get_ddl('VIEW','dsv') FROM DUAL;
DBMS_METADATA.GET_DDL('VIEW','DSV')
--------------------------------------------------------------------------------
CREATE OR REPLACE FORCE EDITIONABLE VIEW "TEST"."dsv" ("OWNER", "NAME", "TYPE
Even uppercase and lowercase plays a major part in matching the syntax
kIsH@Xhydra<>create materialized view dsmv1 as select * from "DSV";
create materialized view dsmv1 as select * from "DSV"
*
ERROR at line 1:
ORA-00942: table or view does not exist
kIsH@Xhydra<>create materialized view dsmv1 as select * from "dsv";
Materialized view created.