Datapump Import (Impdp) Mview Refresh Groups Fails With ORA-01031: Insufficient Privileges
(Doc ID 2614084.1)
Last updated on APRIL 17, 2023
Applies to:
Oracle Database - Enterprise Edition - Version 11.2.0.4 and laterInformation in this document applies to any platform.
Symptoms
When attempting to import using Datapump and creating the refresh group mview fails with ORA-31685:
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 |