Re: ORA-01031 enable query rewrite MV

Hi

I also use other dba users and fail the same.

mview log is owned by the user

select log_owner, master, log_table from dba_mview_logs

LOG_OWNER                      MASTER               LOG_TABLE
------------------------------ -------------------- --------------------
LSC                            T1                   MLOG$_T1

> Why are you using sys? Dodgy practice if you ask me.
>
> First thing that stands out is the mview log is not owned by the user.
>
> Sent from my iPhone
>
> > On 16 May 2023, at 20:26, Ls Cheng <exriscer_at_gmail.com> wrote:
> >
> > 
> > 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