Re: ORA-01031 enable query rewrite MV

Does the following query, that shows inconsistencies in data dictionary,return anything?

SELECT sowner, vname FROM sys.snap_refop$   WHERE operation# IN (21, 22)
  group by sowner,vname
  having count(*) = 1 ;
Best regards,
Nenad
Von meinem iPhone gesendet

> Am 16.05.2023 um 21:25 schrieb Ls Cheng <exriscer_at_gmail.com>:
>
> 
> Hi
>
> I am trying to create a MV on a prebuilt table in Oracle 19c, running the create statements in SYS for user lsc, at the bottom are the DDL's. The problem is the MV create statement fails with ORA-01031 because of the ENABLE QUERY REWRITE clause in the create statement however the user already has the required privileges. Anyone know if I am missing any privilege?
>
> Thanks
>
>
> -- connected as SYS
> create user lsc identified by lsc;
> grant connect, resource, unlimited tablespace to lsc;
> grant create TABLE to lsc;
> grant create MATERIALIZED view to lsc;
> grant on commit refresh to lsc;
> grant global query rewrite to lsc;
>
> create table lsc.t1
> as
> select sysdate mydate, a.*
> from dba_objects a;
>
> CREATE MATERIALIZED VIEW LOG ON lsc.t1 with rowid, SEQUENCE (
> MYDATE ,
> OWNER ,
> OBJECT_NAME ,
> SUBOBJECT_NAME ,
> OBJECT_ID ,
> DATA_OBJECT_ID ,
> OBJECT_TYPE ,
> CREATED ,
> LAST_DDL_TIME ,
> TIMESTAMP ,
> STATUS ,
> TEMPORARY ,
> GENERATED ,
> SECONDARY ,
> NAMESPACE ,
> EDITION_NAME ,
> SHARING ,
> EDITIONABLE ,
> ORACLE_MAINTAINED ,
> APPLICATION ,
> DEFAULT_COLLATION ,
> DUPLICATED ,
> SHARDED ,
> CREATED_APPID ,
> CREATED_VSNID ,
> MODIFIED_APPID ,
> MODIFIED_VSNID
> )
> including new values;
>
>
> create table lsc.t1_mv
> as
> select owner, count(*) cnt, sum(object_id) sum_object_id
> from lsc.t1
> group by owner;
>
> create materialized view lsc.t1_mv
> ON PREBUILT TABLE WITHOUT REDUCED PRECISION
> USING INDEX
> REFRESH FAST ON COMMIT
> USING DEFAULT LOCAL ROLLBACK SEGMENT
> USING ENFORCED CONSTRAINTS DISABLE ON QUERY COMPUTATION
> ENABLE QUERY REWRITE
> as
> (
> select owner, count(*) cnt, sum(object_id) sum_object_id
> from lsc.t1
> group by owner
> );
>
>

--
http://www.freelists.org/webpage/oracle-l