When I try to create materialized views I am getting the following error : ORA-01031: insufficient privileges
Here is the script I am using :
create materialized view cdr_hourly
SELECT TO_CHAR(START_TIME,'HH24') hours, COUNT(*) noofrecs
WHERE START_TIME >= SYSDATE-10 AND START_TIME <= SYSDATE+31
AND SERVICE_TYPE = 2
GROUP BY TO_CHAR(START_TIME,'HH24');
I granted GLOBAL QUERY REWRITE and
QUERY REWRITE privileges. No DBA role. In order to create MV what should be the minimum requirements. Do I need to set any init.ora parameters? Do I need to grant DBA role?
You need another sys privilege of CREATE MATERIALIZED VIEW.
And also check either you are owner of the table or you have select privilege on it?
Thanks. It works with create any materialized view privilege.