Home Can we create Materialized view from view with force and editionable options in oracle?

Questions

Can we create Materialized view from view with force and editionable options in oracle?

April 11, 2022

I am new to oracle. I am using oracle database 19c. I try to create a materialized view from a view. But i get "ORA-00942" error. Does this mean a mview cannot be created from a force editionable view or it is possible to create with any additional privileges? Thanks in advance.

sql>conn kish/password
Connected.
sql>create table ds as select * from dba_source;

Table created.

sql>create or replace force editionable view "dsv" as select * from ds;

View created.

sql>create materialized view dsmv as select * from dsv;
create materialized view dsmv as select * from dsv
                                               *
ERROR at line 1:
ORA-00942: table or view does not exist

>Solution :

"dsv" is different from dsv. In Oracle, never use double quotes (unless you have a really, really good reason – and this isn’t one).

Remove double quotes, entirely.

SQL> create view dsv as select * from dept;

View created.

SQL> create materialized view dsmv as select * from dsv;

Materialized view created.

SQL> select * from dsmv;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

SQL>