Home » Server Options » Replication » MV fast refresh fails with 01031: insufficient privileges (Oracle Database 11g Release 11.2.0.3.0 - 64bit Production)
MV fast refresh fails with 01031: insufficient privileges [message #606465] Fri, 24 January 2014 05:11 Go to next message
Buchas
Messages: 101
Registered: March 2006
Senior Member
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.
Re: MV fast refresh fails with 01031: insufficient privileges [message #606615 is a reply to message #606465] Mon, 27 January 2014 05:42 Go to previous message
Buchas
Messages: 101
Registered: March 2006
Senior Member
The problema was in
GRANT SELECT ON polisas.MLOG$_POLISAS_T TO PUBLIC;


Should have looked up the actual log name in ALL_SNAPSHOT_LOGS which happened to be MLOG$_POLISAS_T1.

Problem solved.

I knew that, really, I found my email explaining that ~11 months ago, oh why do I forget such things? Confused
Previous Topic: Refresh Materialized Views which are dependent on others MV
Next Topic: synchronise data
Goto Forum:
  


Current Time: Thu Mar 28 09:13:56 CDT 2024