Select With Local Function and Remote Tables Using a Dblink Hangs Due To Enq DX
(Doc ID 730423.1)
Last updated on FEBRUARY 14, 2022
Applies to:
Oracle Database - Enterprise Edition - Version 9.2.0.1 to 11.2.0.1 [Release 9.2 to 11.2]Oracle Database Cloud Schema 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
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.
Symptoms
-- Problem Statement:
Two databases interacting via a private DBLINK.
The query at the LOCAL Site includes references for remote objects (via a dblink) as well as a LOCAL FUNCTION (that does not exist at the remote Site):
.
.
Blockers
~~~~~~~~
Resource Holder State
Enq DX-0000001C-00000000 17: waiting for 'SQL*Net more data to client'
.
.
PROCESS 17:
----------------
.
.
SO: c00000032b285470, type: 4, owner: c00000032b1dd670, flag: INIT/-/-/0x00
(session) sid: 93 trans: c00000032a75c1c8, creator: c00000032b1dd670, flag:
(41) USR/- BSY/-/-/-/-/-
DID: 0001-0011-0000CAE4, short-term DID: 0000-0000-00000000
txn branch: c00000032a794db0
oct: 3, prv: 0, sql: c0000003267ce3d0, psql: c000000326883d68, user: 39/<Schema>
O/S info: user: XXX, term: pts/tb, ospid: 11772, machine: YYY
program: oracle@<dblink.domain name> (TNS V1-V3)
application name: oracle@<dblink.domain name>(TNS V1-V3), hash value=0
waiting for 'SQL*Net more data to client' blocking sess=0x0000000000000000
seq=138 wait_time=0 seconds since wait started=681
driver id=54435000, #bytes=7cf, =0
.
.
SO: c000000326441838, type: 53, owner: c00000032b285470, flag: INIT/-/-/0x00
LIBRARY OBJECT LOCK: lock=c000000326441838 handle=c0000003267ce3d0 mode=N
call pin=0000000000000000 session pin=0000000000000000 hpc=0000 hlc=0000
htl=c0000003264418b8[c0000003267c39b8,c000000326447e30] htb=c0000003267c39b8 ssga=c0000003267c2dc0 user=c00000032b285470 session=c00000032b285470 count=1 flags=[0000] savepoint=0x48447c16
LIBRARY OBJECT HANDLE: handle=c0000003267ce3d0 mtx=c0000003267ce500(1)
cdp=1
name=SELECT "col 3","Col 2" FROM "<Table 2>".
"<col 2>" "PP"
hash=bd1ceef17a931288bed1ec5cc36ca4ae timestamp=05-15-2008 11:21:50
.
.
PROCESS 18:
---------------
.
.
SO: c00000032b293490, type: 4, owner: c00000032b1dde60, flag: INIT/-/-/0x00
(session) sid: 104 trans: 0000000000000000, creator: c00000032b1dde60, flag:
(c0000041) USR/- BSY/-/-/-/-/-
DID: 0000-0000-00000000, short-term DID: 0000-0000-00000000
txn branch: c00000032a794ef0
oct: 0, prv: 0, sql: 0000000000000000, psql: 0000000000000000, user: 39/<Schema>
O/S info: user: oracle, term: pts/tb, ospid: 11772, machine: psdev1
program: oracle@<dblink> (TNS V1-V3)
application name: oracle@<dblink> (TNS V1-V3), hash value=0
waiting for 'enq: DX - contention' blocking sess=0xc00000032b285470 seq=9
wait_time=0 seconds since wait started=680
name|mode=44580006, transaction entry #=1c, 0=0
Dumping Session Wait History
for 'enq: DX - contention' count=1 wait_time=2939328
name|mode=44580006, transaction entry #=1c, 0=0
.
.
Due to this lock, the query hangs.
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 |
| References |