Re: ORA-01031 enable query rewrite MV

Hi

oh yes now it works, the only difference is that after granting the select from another user lsc session the DBA session did not reconnect (in your test you reconnected), if I reconnect the MV gets created

Thanks!

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

> Well i did it with another dba user, but in my environment it works
>
> ❯ sqlplus scott/tiger
>
> SQL*Plus: Release 19.0.0.0.0 - Production on Tue May 16 22:31:00 2023
> Version 19.14.0.0.0
>
> Copyright (c) 1982, 2021, Oracle. All rights reserved.
>
> Last Successful login time: Tue May 16 2023 22:30:19 +02:00
>
> Connected to:
> Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
> Version 19.14.0.0.0
>
> SQL> show user
> USER is "SCOTT"
> SQL> select * from session_roles where role='DBA';
>
> ROLE
>
> --------------------------------------------------------------------------------
> DBA
>
> SQL> _at_t
> SQL> -- connected as SYS
> SQL> create user lsc identified by lsc;
>
> User created.
>
> SQL> grant connect, resource, unlimited tablespace to lsc;
>
> Grant succeeded.
>
> SQL> grant create TABLE to lsc;
>
> Grant succeeded.
>
> SQL> grant create MATERIALIZED view to lsc;
>
> Grant succeeded.
>
> SQL> grant on commit refresh to lsc;
>
> Grant succeeded.
>
> SQL> grant global query rewrite to lsc;
>
> Grant succeeded.
>
> SQL>
> SQL> create table lsc.t1
> 2 as
> 3 select sysdate mydate, a.*
> 4 from dba_objects a;
>
> Table created.
>
> SQL>
> SQL> CREATE MATERIALIZED VIEW LOG ON lsc.t1 with rowid, SEQUENCE (
> 2 MYDATE ,
> 3 OWNER ,
> 4 OBJECT_NAME ,
> 5 SUBOBJECT_NAME ,
> 6 OBJECT_ID ,
> 7 DATA_OBJECT_ID ,
> 8 OBJECT_TYPE ,
> 9 CREATED ,
> 10 LAST_DDL_TIME ,
> 11 TIMESTAMP ,
> 12 STATUS ,
> 13 TEMPORARY ,
> 14 GENERATED ,
> 15 SECONDARY ,
> 16 NAMESPACE ,
> 17 EDITION_NAME ,
> 18 SHARING ,
> 19 EDITIONABLE ,
> 20 ORACLE_MAINTAINED ,
> 21 APPLICATION ,
> 22 DEFAULT_COLLATION ,
> 23 DUPLICATED ,
> 24 SHARDED ,
> 25 CREATED_APPID ,
> 26 CREATED_VSNID ,
> 27 MODIFIED_APPID ,
> 28 MODIFIED_VSNID
> 29 )
> 30 including new values;
>
> Materialized view log created.
>
> SQL>
> SQL>
> SQL> create table lsc.t1_mv
> 2 as
> 3 select owner, count(*) cnt, sum(object_id) sum_object_id
> 4 from lsc.t1
> 5 group by owner;
>
> Table created.
>
> SQL>
> SQL> create materialized view lsc.t1_mv
> 2 ON PREBUILT TABLE WITHOUT REDUCED PRECISION
> 3 USING INDEX
> 4 REFRESH FAST ON COMMIT
> 5 USING DEFAULT LOCAL ROLLBACK SEGMENT
> 6 USING ENFORCED CONSTRAINTS DISABLE ON QUERY COMPUTATION
> 7 ENABLE QUERY REWRITE
> 8 as
> 9 (
> 10 select owner, count(*) cnt, sum(object_id) sum_object_id
> 11 from lsc.t1
> 12 group by owner
> 13 );
> create materialized view lsc.t1_mv
> *
> ERROR at line 1:
> ORA-01031: insufficient privileges
>
>
> SQL> connect lsc/lsc
> Connected.
> SQL> grant select on lsc.t1_mv to scott with grant option;
>
> Grant succeeded.
>
> SQL> connect scott/tiger
> Connected.
> SQL> _at_a
> SQL> set echo on
> SQL> create materialized view lsc.t1_mv
> 2 ON PREBUILT TABLE WITHOUT REDUCED PRECISION
> 3 USING INDEX
> 4 REFRESH FAST ON COMMIT
> 5 USING DEFAULT LOCAL ROLLBACK SEGMENT
> 6 USING ENFORCED CONSTRAINTS DISABLE ON QUERY COMPUTATION
> 7 ENABLE QUERY REWRITE
> 8 as
> 9 (
> 10 select owner, count(*) cnt, sum(object_id) sum_object_id
> 11 from lsc.t1
> 12 group by owner
> 13 );
>
> Materialized view created.
>
> SQL>
>
>
> Regards
>
> Maxim
>
> On Tue, May 16, 2023 at 10:26 PM Ls Cheng <exriscer_at_gmail.com> wrote:
>
>> 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