Materialized View Refresh Fails With ORA-32345 When Synonyms and Database Links Are Used

(Doc ID 1546198.1)

Last updated on JULY 06, 2017

Applies to:

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

Symptoms

If there are synonyms and database links in the materialized view,it may fail with ORA-32345 even if the there is no change in synonym

This can be illustrate with a simple example as:

SQL>CREATE TABLE aa( xx VARCHAR2(1));

SQL>CREATE TABLE bb( xx VARCHAR2(1));

SQL>CREATE VIEW v_aa AS SELECT xx FROM aa;


SQL>CREATE VIEW v_bb AS SELECT xx FROM bb;


SQL>CREATE SYNONYM v1 FOR v_aa;
SQL>CREATE SYNONYM v2 FOR v_bb;

SQL>create database link linkname connect to muser identified
by muser using '&tnsname';

SQL>CREATE MATERIALIZED VIEW mv1
AS
SELECT A.xx
FROM
v1@linkname A
,v2@linkname B;

SQL>exec dbms_mview.refresh('mv1');

ERROR at line 1:
ORA-32345: fail to refresh the materialized view MUSER.MV1 due to
the changed synonym
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2251
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2457
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2426
ORA-06512: at line 1

Stack can be match the following:

STACK TRACE:
------------
skdstdst <- ksedst1 <- ksedst <- dbkedDefDump <- ksedmp
<- dbkdaKsdActDriver <- dbgdaExecuteAction <- dbgdaRunAction
<- dbgdRunActions <- dbgdProcessEventActions
<- dbgdChkEventKgErr <- dbkdChkEventRdbmsErr <- PGOSF28_ksfpec
<- dbgePostErrorKGE <- dbkePostKGE_kgsf
<- kgesev <- ksesec2 <- kkzpgcinfo <- kkzpcbk <- opikkzprs1
<- opiSem <- opiprs <- kksParseChildCursor <- rpiswu2 <- kksLoadChild
<- kxsGetRuntimeLock <- kksfbc <- kkspsc0 <- kksParseCursor <- opiosq0
<- opiall0 <- opikpr <- opiodr <- PGOSF106_rpidrus <- skgmstack
<- rpidru <- rpiswu2 <- kprball <- kprbprsu <- kprbprs
<- kkzparse <- kkzfcsrc <- kkzfrsh <- kkzifr3g <- spefcmpa
<- spefmccallstd <- pextproc <- PGOSF294_peftrusted <- psdexsp <- rpiswu2
<- psdextp <- pefccal <- pefcal <- pevm_FCAL <- pfrinstr_FCAL
<- pfrrun_no_tool <- pfrrun <- plsql_run <- peicnt <- kkxexe
<- opiexe <- kpoal8 <- opiodr <- ttcpip <- opitsk
<- opiino <- opiodr <- opidrv <- sou2o <- opimai_real
<- ssthrdmain <- main <- libc_start_main <- start



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