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