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

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

CodePudding user response：

"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>