Hello.
I have two schemas: POLISAS and DIMENSIONS. POLISAS has a table POLISAS_T and MV log created as:
CREATE MATERIALIZED VIEW LOG ON polisas.POLISAS_T WITH PRIMARY KEY, ROWID, SEQUENCE;
GRANT SELECT ON polisas.MLOG$_POLISAS_T TO PUBLIC;
GRANT SELECT ON polisas.POLISAS_T TO PUBLIC;
I need to create a MV with FAST REFRESH in DIMENSIONS schema. This user has the following rights:
select 'GRANT '||P.PRIVILEGE||' TO '||P.GRANTEE||';' FROM dba_SYS_privs P WHERE grantee IN ( 'DIMENSIONS')
gives
GRANT ALTER ANY TABLE TO DIMENSIONS;
GRANT CREATE VIEW TO DIMENSIONS;
GRANT ON COMMIT REFRESH TO DIMENSIONS;
GRANT GLOBAL QUERY REWRITE TO DIMENSIONS;
GRANT UNLIMITED TABLESPACE TO DIMENSIONS;
GRANT COMMENT ANY TABLE TO DIMENSIONS;
GRANT CREATE TABLE TO DIMENSIONS;
GRANT CREATE SESSION TO DIMENSIONS;
GRANT CREATE MATERIALIZED VIEW TO DIMENSIONS;
However when I try creating MV with:
CREATE MATERIALIZED VIEW DIMENSIONS.POLICY_T
NOLOGGING TABLESPACE USERS
PARALLEL
USING INDEX TABLESPACE USERS
REFRESH fast
AS
SELECT
POL.ID
FROM
POLISAS.POLISAS_T POL
WHERE
POL.STATUSAS = 1
I receive error :
ORA-12018: following error encountered during code generation for "DIMENSIONS"."POLICY_T"
ORA-01031: insufficient privileges
However I seem to have granted all the required privileges, as I can successfullt create MV with FAST REFRESH with the following script:
CREATE USER USR_WITH_TABLE IDENTIFIED BY USR_WITH_TABLE
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP;
GRANT RESOURCE TO USR_WITH_TABLE ;
GRANT CREATE SESSION TO USR_WITH_TABLE ;
GRANT CREATE TABLE TO USR_WITH_TABLE;
CREATE USER USR_WITH_MV IDENTIFIED BY USR_WITH_MV
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP;
GRANT RESOURCE TO USR_WITH_MV ;
GRANT CREATE SESSION TO USR_WITH_MV;
GRANT ALTER ANY TABLE TO USR_WITH_MV;
GRANT CREATE MATERIALIZED VIEW TO USR_WITH_MV;
GRANT CREATE VIEW TO USR_WITH_MV;
connect USR_WITH_TABLE/USR_WITH_TABLE@DB
create table USR_WITH_TABLE.test_tbl_t as select * from v$session where rownum = 1;
alter table USR_WITH_TABLE.test_tbl_t add constraint PK_test_tbl primary key (SID) USING INDEX TABLESPACE INDX;
CREATE MATERIALIZED VIEW LOG ON USR_WITH_TABLE.test_tbl_t WITH PRIMARY KEY, ROWID, SEQUENCE;
GRANT SELECT ON USR_WITH_TABLE.test_tbl_t TO USR_WITH_MV;
GRANT SELECT ON USR_WITH_TABLE.MLOG$_test_tbl_t TO USR_WITH_MV;
CONNECT USR_WITH_MV/USR_WITH_MV@DB;
CREATE MATERIALIZED VIEW USR_WITH_MV.TEST_MV
NOLOGGING TABLESPACE USERS
PARALLEL
USING INDEX TABLESPACE USERS
REFRESH fast
AS
SELECT SID FROM USR_WITH_TABLE.test_tbl_t;
CONNECT SYSTEM/SYSTEMPSW@DB;
DROP USER USR_WITH_TABLE CASCADE;
DROP USER USR_WITH_MV CASCADE;
I find myself confused. Why does it work with my test case, but not in the real situation?
As a sidenote, I find it strange, that Oracle takes ~2 minutes to throw the "ORA-01031: insufficient privileges" error. Usually Oracle checks privileges before starting to process the SQL statement... Why does it take so long in my case?
Help please.