My Oracle Support Banner

Datapump Import (Impdp) Mview Refresh Groups Fails With ORA-01031: Insufficient Privileges (Doc ID 2614084.1)

Last updated on NOVEMBER 27, 2019

Applies to:

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

Symptoms

When attempting to import using Datapump and creating the refresh group mview fails with ORA-31685:

ORA-31685: Object type REFRESH_GROUP:"<SCHEMA_NAME>"."<MV_NAME>" failed due to insufficient privileges. Failing sql is:
BEGIN dbms_refresh.make('"<SCHEMA_NAME>"."<MV_NAME>"',list=>null,next_date=>'2019-10-22 22:00:01',interval=>'SYSDATE + 5/(24*3600)',
implicit_destroy=>TRUE,lax=>FALSE,joed_rpc=>TRUE,refresh_after_errors=>FALSE,purge_option=>1,parallelism=>0,heap_size=>0);
dbms_refresh.add(name=>':"<SCHEMA_NAME>"."<MV_NAME>",list=>'"<SCHEMA_NAME>"."<MV_NAME>"',siteid=>0,export_db=>'SO


Attempting to manually create the refresh group mview, also fails even as the refresh group owner and even after grant DBA to the mview owner:

SQL>GRANT CREATE MATERIALIZED VIEW TO schema with admin option;
Grant succeeded.

SQL>set echo on
SQL>@<SCRIPT_NAME>.sql
SQL>DECLARE
  2 SnapArray SYS.DBMS_UTILITY.UNCL_ARRAY;
  3 BEGIN
  4 Begin
  5 SnapArray(1) := '"<SCHEMA_NAME>"."<MV_NAME>"';
  6 SnapArray(2) := NULL;
  7 SYS.DBMS_REFRESH.MAKE (
  8 name => '"<SCHEMA_NAME>"."<MV_NAME>"'
  9 ,tab => SnapArray
 10 ,next_date => TO_DATE('11/11/2019 18:18:35', 'MM/DD/YYYY HH24:MI:SS')
 11 ,interval => 'SYSDATE + 5/(24*3600) '
 12 ,implicit_destroy => TRUE
 13 ,lax => TRUE
 14 ,job => 0
 15 ,rollback_seg => NULL
 16 ,push_deferred_rpc => TRUE
 17 ,refresh_after_errors => FALSE
 18 ,purge_option => 1
 19 ,parallelism => 0
 20 ,heap_size => 0
 21 );
 22 Commit;
 23 exception
 24 when others then
 25 begin
 26 raise;
 27 end;
 28 end;
 29 END;
 30 /
DECLARE
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at line 26

Changes

Dataguard had been enabled.

Cause

To view full details, sign in with your My Oracle Support account.

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


In this Document
Symptoms
Changes
Cause
Solution


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.