Create Materialized View fails with ORA-01031 in Logical Standby Environment (Doc ID 394248.1)

Last updated on OCTOBER 01, 2013

Applies to:

Oracle Database - Enterprise Edition - Version 9.2.0.6 and later
Information in this document applies to any platform.

Symptoms

Materialized view creation with automatic refresh option in a logical standby database
fails with error

ORA-01031: insufficient privileges

eg:-

CREATE MATERIALIZED VIEW "EMP_MV"
REFRESH FORCE
START WITH SYSDATE
NEXT trunc(trunc(sysdate)+1)+1
AS
select * from EMP@ISC.ORACLE.COM;

select * from EMP@ISC.ORACLE.COM
*
ERROR at line 6:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_LOGSTDBY", line 24
ORA-06512: at "SYS.DBMS_JOB", line 122
ORA-06512: at "SYS.DBMS_IREFRESH", line 186
ORA-06512: at "SYS.DBMS_IREFRESH", line 369
ORA-06512: at "SYS.DBMS_REFRESH", line 88
ORA-06512: at "SYS.DBMS_REFRESH", line 62
ORA-06512: at "SYS.DBMS_ISNAPSHOT", line 76
ORA-01403: no data found
ORA-06512: at line 1

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms