ORA-02047 Error when Creating materialized view base on Read-only Database (Doc ID 2964646.1)

Last updated on JULY 20, 2024

Applies to:

Symptoms

On : 19.6.0.0.0 version, Linux x86-64

ORA-02047 Error when Creating materialized view base on Read-only Database

ERROR
-----------------------

ERROR at line 4:
ORA-02047: cannot join the distributed transaction in progress
ORA-06512: at line 1

STEPS
-----------------------
This problem can be reproduced by the following test case：

1. dblink01 is a read-only database.

2. Pre-created test table test.test01 on read-only database.

Create table test01
( col1 number,
col2 varchar2(20) NOT NULL ENABLE,
col3 varchar2(20) NOT NULL ENABLE,
col4 varchar2(20) NOT NULL ENABLE,
CONSTRAINT "test001pk1" PRIMARY KEY (col2,col3)
)
/

3. Execute create materialized view:

CREATE MATERIALIZED VIEW mview_m1
REFRESH FORCE ON DEMAND
AS
select * from test.test01@dblink01;

Errorstack shows：

----- Error Stack Dump -----
<error barrier> at 0x7ffe9f97dd50 placed dbkda.c@296
ORA-02047: cannot join the distributed transaction in progress
----- Current SQL Statement for this session (sql_id=8bn2wvyruj1a3) -----
begin sys.dbms_snapshot_utl.set_up@"dblink01" (:mown_col, :mas_col, :rollseg_col, :flag_col, :outflag_col, :snaptime_col, :loadertime_col, :num_sid, :snapid_tab, :max_tab_scn_col, :max_dl_scn_col, :max_pmop_scn_col, :trunc_scn_col, :max_ins_tim_col, :max_upd_tim_col, :max_del_tim_col, :max_ins_scn_col, :max_upd_scn_col, :max_del_scn_col, :hdl_col, :hdl_indx_col, :rscn); end;

----- PL/SQL Call Stack -----

ksedst1 <- ksedst <- dbkedDefDump <- ksedmp <- dbkdaKsdActDriver
<- dbgdaExecuteAction <- dbgdaRunAction <- dbgdRunActions <- dbgdProcessEventAct <- ions
<- dbgdChkEventKgErr <- dbkdChkEventRdbmsEr <- dbgePostErrorKGE <- 1066 <- dbkePostKGE_kgsf
<- kgeade <- kgeselv <- ksesecl0 <- npibeg <- npixfc
<- psdrpc <- prirpc <- pevm_RCAL <- pfrinstr_RCAL <- pfrrun_no_tool
<- pfrrun <- plsql_run <- peicnt <- kkxexe <- opiexe
<- opiall0 <- opikpr <- opiodr <- rpidrus <- skgmstack
<- rpidru <- rpiswu2 <- kprball <- kkzfcbsu <- kkzsav
<- kkzcsn <- opiexe <- opiosq0 <- kpooprx <- kpoal8
<- opiodr <- ttcpip <- opitsk <- opimai_real <- ssthrdmain
<- main <- libc_start_main <- start

Changes

 None

Cause

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