RE: ORA-01031 enable query rewrite MV

Unsubscribe  

From: oracle-l-bounce_at_freelists.org <oracle-l-bounce_at_freelists.org> On Behalf Of Nenad Noveljic Sent: Tuesday, May 16, 2023 4:15 PM
To: exriscer_at_gmail.com
Cc: Oracle Mailinglist <oracle-l_at_freelists.org> Subject: Re: ORA-01031 enable query rewrite MV  

Does the following query, that shows inconsistencies in data dictionary,return anything?  

SELECT sowner, vname FROM sys.snap_refop$   WHERE operation# IN (21, 22)
  group by sowner,vname
  having count(*) = 1 ;

Best regards,

Nenad

Von meinem iPhone gesendet

Am 16.05.2023 um 21:25 schrieb Ls Cheng <exriscer_at_gmail.com <mailto:exriscer_at_gmail.com> >:



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    ,

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