Re: ORA-01031 enable query rewrite MV

You need grant select on lsc.t1_mv to sys with grant option (see the doc. 1326117.1

Regards

Maxim

On Tue, May 16, 2023 at 10:13 PM Ls Cheng <exriscer_at_gmail.com> wrote:

> 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
>
>
> Thanks
>
>
>
> On Tue, May 16, 2023 at 10:03 PM Dominic Brooks <dombrooks_at_hotmail.com>
> wrote:
>
>> 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