Hi there,
I met the "ORA-01031: insufficient privileges" error, while Creating Materialized View in different database.
i am doing Migration from one Source_DB to Target_DB, with syncup
-------------------------------------------------------------------------------------------------------------------
--Step1-- Create empty tables
CREATE TABLE TRGUSER.TABLE_TEST TABLESPACE USER_DATA01
AS SELECT * FROM SRCUSER.TABLE_TEST@BDLINK WHERE 1=0;
------------------------
Q_RESULT-- TABLE CREATED
------------------------
--Step2-- insert into over database link where task_id of interest
insert into TRGUSER.TABLE_TEST (C1_KEY, C2_ID, C3, C4, C5, C6)
select C1_KEY, C2_ID, C3, C4, C5, C6
FROM SRCUSER.TABLE_TEST@BDLINK;
------------------------
Q_RESULT--INSERT SUCESSFULL
------------------------
--Step3--create snapshot on prebuilt table
-- MV_LOG on source DB
CREATE MATERIALIZED VIEW LOG ON "SRCUSER"."TABLE_TEST";
------------------------
Q_RESULT-- MV LOG CREATED
------------------------
-- MV on target
CREATE MATERIALIZED VIEW TRGUSER.TABLE_TEST
ON PREBUILT TABLE REFRESH FAST ON COMMIT ENABLE QUERY REWRITE
AS SELECT C1_KEY, C2_ID, C3, C4, C5, C6 FROM SRCUSER.PLRS_AUDIT_TABLE@LACSH;
------------------------
Q_RESULT-- ORA-01031: insufficient privileges
------------------------ ---------------------------------------------------------------------------------------------------------------------
I have given all the grants
GRANT CONNECT TO TGRGUSER;
GRANT RESOURCE TO TGRGUSER;
GRANT ALTER ANY TABLE TO TGRGUSER;
GRANT CREATE VIEW TO TGRGUSER;
GRANT ON COMMIT REFRESH TO TGRGUSER;
GRANT GLOBAL QUERY REWRITE TO TGRGUSER;
GRANT COMMENT ANY TABLE TO TGRGUSER;
GRANT CREATE TABLE TO TGRGUSER;
GRANT CREATE SESSION TO TGRGUSER;
GRANT CREATE MATERIALIZED VIEW TO TGRGUSER;
------------------------
However still got the smae error "insufficient privileges"
Than i removed "ON COMMIT ENABLE QUERY REWRITE"
MATERIALIZED VIEW created successfully
My question is
Que1.) is this the only solution ?
Que2.) any other solution where i can keep "ON COMMIT ENABLE QUERY REWRITE" while creating materialized view ?
Many thanks in advance
This is a documented restriction:
Restrictions on Refreshing ON COMMIT
...
This clause is not supported for materialized views with remote tables. https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/CREATE-MATERIALIZED-VIEW.html#GUID-EE262CA4-01E5-4618-B659-6165D993CA1B So you'll need to go with ON DEMAND refresh.
Though it appears you'll need to create the MV first. Then alter it to be ON DEMAND:
grant connect, resource, alter any table,
create view, on commit refresh, global query rewrite,
comment any table, create table, create session,
create materialized view, unlimited tablespace
to TRGUSER
identified by TRGUSER;
grant create session, create table, unlimited tablespace
to SRCUSER
identified by SRCUSER;
conn SRCUSER/SRCUSER
create table table_test (
C1_KEY int primary key, C2_ID int, C3 int, C4 int, C5 int, C6 int
);
create materialized view log on table_test
with primary key ( c2_id, c3, c4, c5, c6 )
including new values;
grant select on table_test to trguser;
conn TRGUSER/TRGUSER
CREATE TABLE TRGUSER.TABLE_TEST
AS SELECT * FROM SRCUSER.TABLE_TEST@loopback WHERE 1=0;
insert into trguser.table_test (c1_key, c2_id, c3, c4, c5, c6)
select c1_key, c2_id, c3, c4, c5, c6
from srcuser.table_test@loopback;
create materialized view trguser.table_test
on prebuilt table
refresh fast on demand
enable query rewrite
as
select c1_key, c2_id, c3, c4, c5, c6
from srcuser.table_test@loopback;
ORA-12018: following error encountered during code generation for "TRGUSER"."TABLE_TEST"
ORA-00942: table or view does not exist
create materialized view trguser.table_test
on prebuilt table enable query rewrite
as
select c1_key, c2_id, c3, c4, c5, c6
from srcuser.table_test@loopback;
Materialized view TRGUSER.TABLE_TEST created.
alter materialized view trguser.table_test
refresh fast on demand ;
Materialized view TRGUSER.TABLE_TEST altered.