My Oracle Support Banner

Materialized View Refresh Fails With ORA-32345 When Synonyms and Database Links Are Used (Doc ID 1546198.1)

Last updated on MARCH 10, 2020

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.5 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A 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

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
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.