ORA-01031 enable query rewrite MV
Date: Tue, 16 May 2023 21:24:32 +0200
Message-ID: <CAJ2-Qb9UxNm3m=M2QLVjMSdy1M6eCJGmTxmkqN+FdsN_L0ydkw_at_mail.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
create table lsc.t1
CREATE MATERIALIZED VIEW LOG ON lsc.t1 with rowid, SEQUENCE (
create table lsc.t1_mv
as
select sysdate mydate, a.*
from dba_objects a;
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;
as
select owner, count(*) cnt, sum(object_id) sum_object_id
from lsc.t1
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-lReceived on Tue May 16 2023 - 21:24:32 CEST