Re: ORA-01031 enable query rewrite MV

Hi

SQL> SELECT sowner, vname FROM sys.snap_refop$   2 WHERE operation# IN (21, 22)
  3 group by sowner,vname
  4 having count(*) = 1 ;

no rows selected

I test this in 12.1.0.2 database as well with same error

The problem is ENABLE QUERY REWRITE, if I remove it the MV creates perfectly but the user already has grant global query rewrite to lsc;

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
);

Thanks

On Tue, May 16, 2023 at 10:15 PM Nenad Noveljic <nenad.noveljic_at_gmail.com> wrote:

> 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