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@xxxxxxxxx> 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@xxxxxxxxxxx>
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@xxxxxxxxx> wrote:the create statements in SYS for user lsc, at the bottom are the DDL's. The
Hi
I am trying to create a MV on a prebuilt table in Oracle 19c, running
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
);
