Scenario:

ORA-01031: insufficient privileges raised during the creation of materialized view (MVIEW) on another schema by the SYSTEM user.

CREATE MATERIALIZED VIEW kaysar.mview_test
BUILD IMMEDIATE
AS
(SELECT * from kaysar.test);

[Error] Execution (4: 26): ORA-01031: insufficient privileges

Investigation:

The SYSTEM already has the privilege of CREATE_MATERIALIZED_VIEW to create the MVIEW on another schema but failed to create the MVIEW. It is surprising that a super user can’t do this.

Solution:

Upon dig down deeper it is found that the target schema user KAYSAR need exclusive privilege of CREATE TABLE to be granted though the RESOURCE role contains the create table privilege.

GRANT CREATE TABLE TO KAYSAR;

Note: This scenario is true for any user has DBA role and tries to create MVIEW on another schema.