Re: ORA-01031 enable query rewrite MV

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

SQL> _at_t
SQL> -- connected as SYS
SQL> create user lsc identified by lsc;

  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          ,

 24  SHARDED           ,
 25  CREATED_APPID        ,
 26  CREATED_VSNID        ,
 27  MODIFIED_APPID       ,

Materialized view log created.

SQL>
SQL>
SQL> create table lsc.t1_mv

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

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