Re: ORA-01031 enable query rewrite MV

Hi

I also did grant select on lsc.t1 to sys with grant option; with no luck

On Tue, May 16, 2023 at 10:19 PM Maxim <mdemenko_at_gmail.com> wrote:

> 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