ORA-04020: deadlock inside one single session.
Note: Tested in Oracle 19c
1. Test Setup
drop table test_tab;
drop table test_tab_v1;
drop table test_tab_v2;
create table test_tab as select 'ABC123' name, level c0 from dual connect by level <= 10;
create table test_tab_v1 as select level c0 from dual connect by level <= 5;
create table test_tab_v2 as select level c0 from dual connect by level <= 10;
create or replace function test_fun (p_x number) return number is
l_x number;
begin
select c0 into l_x from test_tab where name = 'ABC123' and c0 = p_x;
return l_x;
end;
/
create or replace package test_pkg is
procedure p1 (p_x number);
end;
/
create or replace package body test_pkg is
procedure p1 (p_x number) is
l_x number;
begin
l_x := test_fun(p_x);
end;
end;
/
create or replace force view test_view as
with sq1 as (select distinct c0 vc0, test_fun(c0) vc1 from test_tab_v1)
select * from test_tab_v2 v2, sq1 v1 where v2.c0 = v1.vc0;
create or replace procedure test_ddl_table(p_cnt number) as
begin
for i in 1..p_cnt loop
execute immediate 'alter table test_tab add(ks_c'||i||' number)';
dbms_session.sleep(0.1);
end loop;
end;
/
create or replace procedure test_ddl_view(p_cnt number) as
begin
for i in 1..p_cnt loop
execute immediate 'alter view test_view compile';
end loop;
end;
/
create or replace procedure test_exec_call(p_cnt number) as
begin
for i in 1..p_cnt loop
test_pkg.p1(mod(i, 10) +1);
dbms_session.sleep(0.1);
end loop;
end;
/
2. Test Run
Open two Sqlplus sessions: SESS-1 and SESS-2.
2.1 In SESS-1, run:
exec test_ddl_table(300);
2.2 In SESS-2, run:
exec test_ddl_view(10000);
ERROR:
ORA-24344: success with compilation error
ORA-06512: at "K.TEST_DDL_VIEW", line 4
ORA-06512: at line 1
select object_name, object_id, data_object_id, object_type, created, last_ddl_time, timestamp, status from dba_objects
where object_name in ('TEST_PKG', 'TEST_FUN', 'TEST_VIEW', 'TEST_TAB', 'TEST_TAB_V1', 'TEST_TAB_V2', 'TEST_DDL_TABLE', 'TEST_DDL_VIEW', 'TEST_EXECC_CALL')
order by status, object_name;
OBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS
-------------- ---------- -------------- ------------ -------------------- -------------------- ------------------- ------------
TEST_FUN 6236412 FUNCTION 2026-JUN-18 11:13:56 2026-JUN-21 08:37:46 2026-06-21:08:37:46 INVALID
TEST_PKG 6236411 PACKAGE BODY 2026-JUN-18 11:11:44 2026-JUN-21 08:37:46 2026-06-21:08:37:46 INVALID
TEST_VIEW 6236423 VIEW 2026-JUN-18 11:28:39 2026-JUN-21 08:38:29 2026-06-21:08:38:29 INVALID
TEST_DDL_TABLE 6236774 PROCEDURE 2026-JUN-20 09:30:49 2026-JUN-20 09:30:49 2026-06-20:09:30:49 VALID
TEST_DDL_VIEW 6236775 PROCEDURE 2026-JUN-20 09:30:49 2026-JUN-20 09:30:49 2026-06-20:09:30:49 VALID
TEST_PKG 6236410 PACKAGE 2026-JUN-18 11:09:46 2026-JUN-20 10:12:43 2026-06-18:11:20:14 VALID
TEST_TAB 6236945 6236945 TABLE 2026-JUN-21 08:37:45 2026-JUN-21 08:38:39 2026-06-21:08:38:39 VALID
TEST_TAB_V1 6236946 6236946 TABLE 2026-JUN-21 08:37:45 2026-JUN-21 08:37:45 2026-06-21:08:37:45 VALID
TEST_TAB_V2 6236947 6236947 TABLE 2026-JUN-21 08:37:46 2026-JUN-21 08:37:46 2026-06-21:08:37:46 VALID
select name, type, '0X'||trim('0' from addr) addr, to_number(addr, 'XXXXXXXXXXXXXXXXXX') addr_num, hash_value, type
from v$db_object_cache v
where name in ('TEST_PKG', 'TEST_FUN', 'TEST_VIEW', 'TEST_TAB', 'TEST_TAB_V1', 'TEST_TAB_V2', 'TEST_DDL_TABLE', 'TEST_DDL_VIEW', 'TEST_EXECC_CALL')
order by v.name;
NAME TYPE ADDR ADDR_NUM HASH_VALUE TYPE
-------------- ------------ ------------ ---------- ---------- ------------
TEST_DDL_TABLE PROCEDURE 0X96A00E48 2527071816 2793044217 PROCEDURE
TEST_DDL_VIEW PROCEDURE 0X6B5A398 1801075072 2396118748 PROCEDURE
TEST_FUN FUNCTION 0X80CDF188 2160980360 2391147328 FUNCTION
TEST_PKG PACKAGE 0X88C948C8 2294892744 3673762546 PACKAGE
TEST_PKG PACKAGE BODY 0X737B9C48 1937480776 2937683311 PACKAGE BODY
TEST_TAB TABLE 0X70F1F1B 1894904240 1905210899 TABLE
TEST_TAB_V1 TABLE 0X71FAA968 1912252776 2228492516 TABLE
TEST_TAB_V2 TABLE 0X97BBD6A8 2545669800 580752620 TABLE
TEST_VIEW VIEW 0X70BF69B8 1891592632 1492527927 VIEW
3. trc file
Following trc file shows:
ORA-04020: deadlock detected while trying to lock object K.TEST_FUN
It is a self session deadlock because waiting session and blocking session are the same (0xb89008b8 17).
Blog Blog Oracle 12c single session "library cache lock (cycle)" deadlock contains more detail analysis on this behaviour,
for example,
waiting session (0xb89008b8 17) = blocking session (0xb89008b8 17)
waiting mode (X) != blocking mode (s)
waiting lock (0x62d127d0) != blocking lock (0x83c3f830)
waiting Flags=[0000] != blocking Flags=CNB/PNC/[0003]
waiting SavepointNum=d56d7 > blocking SavepointNum=d56ab
Trace file /orabin/app/oracle/admin/testdb/diag/rdbms/testdb/testdb/trace/testdb_ora_323537.trc
Version 19.30.0.0.0
Oracle process number: 48
Unix process pid: 323537, image: oracle@testdb
*** SESSION ID:(17.58059) 2026-06-21T08:38:29.493117+02:00
A deadlock among DDL and parse locks is detected.
This deadlock is usually due to user errors in
the design of an application or from issuing a set
of concurrent statements which can cause a deadlock.
This should not be reported to Oracle Support.
The following information may aid in finding
the errors which cause the deadlock:
ORA-04020: deadlock detected while trying to lock object K.TEST_FUN
Short stack dump of current process:
----- Abridged Call Stack Trace -----
ksedsts()+426<-kgllkde()+1182<-kglLockWait()+2503<-kgllkal()+2030<-kglLock()+1426<-kglget()+297<-kqlvld()+851<-kglgob()+3504<-kgldpo0()+726<-qcdlgpo()+772<-qcsRslvPLSQLInvoc1()+979<-qcsRslvPLSQLInvoc()+96<-qcsRslvName()+676<-qcsridn()+115<-qcsraic()+431<-qcspqbDescendents()+546
<-qcspqb()+283<-qcspqbDescendents()+4286<-qcspqb()+283<-kkmdrv()+180<-opiSem()+2167<-opiprs()+528<-kksParseChildCursor()+524<-rpiswu2()+2004<-kksLoadChild()+5339<-kxsGetRuntimeLock()+2335<-kksfbc()+19863<-opiexe()+2982<-opiosq0()+4482<-opipls()+21173<-opiodr()+1264
<-rpidrus()+198<-skgmstack()+65<-rpidru()+151<-rpiswu2()+543<-rpidrv()+1281<-psddr0()+467<-psdnal()+624<-pevm_EXIM()+282<-pfrinstr_EXIM()+43<-pfrrun_no_tool()+60<-pfrrun()+904<-plsql_run()+752<-peicnt()+279<-kkxexe()+720<-opiexe()+31613<-kpoal8()+2301<-opiodr()+1264
<-ttcpip()+1232<-opitsk()+1908<-opiino()+935<-opiodr()+1264<-opidrv()+1094<-sou2o()+165<-opimai_real()+422<-ssthrdmain()+417<-main()+256<-__libc_start_main()+229<-_start()+46
----- End of Abridged Call Stack Trace -----
Partial short call stack signature: 0xce99135a9a693f97
--------------------------------------------------------
object handle waiting session sid waiting lock mode blocking session sid blocking lock mode
---------------- ---------------- ----- --------------- ---- ---------------- ----- --------------- ----
0x80cdf188 0xb89008b8 17 0x62d127d0 X 0xb89008b8 17 0x83c3f830 S
--------------------------------------------------------
---------- DUMP OF WAITING AND BLOCKING LOCKS ----------
--------------------------------------------------------
------------- WAITING LOCK -------------
----------------------------------------
SO: 0x66147068, type: LIBRARY OBJECT LOCK (118), map: 0x62d127d0
state: LIVE (0x4532), flags: 0x1
owner: 0xaefc0968, proc: 0xb8eebc90
link: 0x66147088[0xa5f638e0, 0xaefc09d8]
child list count: 0, link: 0x661470d8[0x661470d8, 0x661470d8]
pg: 0
SOC: 0x62d127d0, type: LIBRARY OBJECT LOCK (118), map: 0x66147068
state: LIVE (0x99fc), flags: INIT (0x1)
LibraryObjectLock: Address=0x62d127d0 Handle=0x80cdf188 RequestMode=X
CanBeBrokenCount=708 Incarnation=708 ExecutionCount=0
User=0xb89008b8 Session=0xb89008b8 ReferenceCount=0
Flags=[0000] SavepointNum=d56d7
LibraryHandle: Address=0x80cdf188 Hash=8e860340 LockMode=S PinMode=S LoadLockMode=0 Status=INVL
ObjectName: Name=K.TEST_FUN
FullHashValue=e7a045fef628c10c7c8dcf7b8e860340 Namespace=TABLE/PROCEDURE(01) Type=FUNCTION(08) ContainerId=0 ContainerUid=0 Identifier=6236412 OwnerIdn=49
Statistics: InvalidationCount=674 ExecutionCount=0 LoadCount=696 ActiveLocks=1 ActivePins=1 TotalLockCount=163897 TotalPinCount=163874
Counters: BrokenCount=708 RevocablePointer=708 KeepDependency=0 Version=0 BucketInUse=63688 HandleInUse=63688 HandleReferenceCount=0
Concurrency: DependencyMutex=0x80cdf238(0, 326246, 0, 0) Mutex=0x80cdf2d8(0, 1479323, 6, 0)
Flags=PIN/TIM/VER/[40002801] Flags2=[0000] HandleIvd=6236945
WaitersLists:
Lock=0x80cdf218[0x62d12800,0x62d12800]
Pin=0x80cdf1f8[0x80cdf1f8,0x80cdf1f8]
LoadLock=0x80cdf270[0x80cdf270,0x80cdf270]
Timestamp: Current=06-21-2026 08:37:46
HandleReference: Address=0x80cdf358 Handle=0xa0670d70 Flags=OWN[200]
LibraryObject: Address=0x903c0190 HeapMask=0000-0015-0015-0000 Flags=EXS/LOC[0004] Flags2=/CSC[4000000] Flags3=[0000] PublicFlags=NST[0001]
DataBlocks:
Block: #='0' name=KGLH0^8e860340 pins=0 Change=NONE
Heap=0x80cdefc8 Pointer=0x903c0270 Extent=0x903c00d0 Flags=I/-/P/A/-/-/-
FreedLocation=0 Alloc=2.179688 Size=3.976562 LoadTime=06-21-2026 06:38:20
Block: #='2' name=PLDIA^8e860340 pins=1 Change=NONE
Heap=0x903c0620 Pointer=0x80763550 Extent=0x80763490 Flags=I/-/P/A/-/-/-
FreedLocation=0 Alloc=13.593750 Size=15.820312 LoadTime=06-21-2026 06:38:20
Block: #='4' name=PLMCD^8e860340 pins=1 Change=NONE
Heap=0x903c0778 Pointer=0x955b7e68 Extent=0x955b7da8 Flags=I/-/P/A/-/-/-
FreedLocation=0 Alloc=2.554688 Size=3.937500 LoadTime=06-21-2026 06:38:20
------------- WAITING SESSION -------------
sid: 17 ser: 58059 audsid: 102774880 user: 49/K
flags: (0x8000041) USR/- flags2: (0x48009) -/DDLT2/INC
flags_idl: (0x1) status: BSY/-/-/- kill: -/-/-/-
pid: 48 O/S info: user: oracle, term: UNKNOWN, ospid: 323537
image: oracle@testdb
client details:
O/S info: user: U000380, term: WCLINBDOXKKDUGS, ospid: 6132:51788
machine: SYS\WCLINBDOXKKDUGS program: sqlplus.exe
application name: SQL*Plus, hash value=3669949024
current SQL:
alter view test_view compile
------------- BLOCKING LOCK ------------
----------------------------------------
SO: 0x87edd848, type: LIBRARY OBJECT LOCK (118), map: 0x83c3f830
state: LIVE (0x4532), flags: 0x1
owner: 0xa5f51dd0, proc: 0xb8eebc90
link: 0x87edd868[0xb8f018c0, 0x87e13a98]
child list count: 0, link: 0x87edd8b8[0x87edd8b8, 0x87edd8b8]
pg: 0
SOC: 0x83c3f830, type: LIBRARY OBJECT LOCK (118), map: 0x87edd848
state: LIVE (0x99fc), flags: INIT (0x1)
LibraryObjectLock: Address=0x83c3f830 Handle=0x80cdf188 Mode=S
CallPin=0x90ab9830 CanBeBrokenCount=708 Incarnation=707 ExecutionCount=0
User=0xb89008b8 Session=0xb89008b8 ReferenceCount=3
Flags=CNB/PNC/[0003] SavepointNum=d56ab
LibraryHandle: Address=0x80cdf188 Hash=8e860340 LockMode=S PinMode=S LoadLockMode=0 Status=INVL
ObjectName: Name=K.TEST_FUN
FullHashValue=e7a045fef628c10c7c8dcf7b8e860340 Namespace=TABLE/PROCEDURE(01) Type=FUNCTION(08) ContainerId=0 ContainerUid=0 Identifier=6236412 OwnerIdn=49
Statistics: InvalidationCount=674 ExecutionCount=0 LoadCount=696 ActiveLocks=1 ActivePins=1 TotalLockCount=163897 TotalPinCount=163874
Counters: BrokenCount=708 RevocablePointer=708 KeepDependency=0 Version=0 BucketInUse=63688 HandleInUse=63688 HandleReferenceCount=0
Concurrency: DependencyMutex=0x80cdf238(0, 326246, 0, 0) Mutex=0x80cdf2d8(0, 1479323, 6, 0)
Flags=PIN/TIM/VER/[40002801] Flags2=[0000] HandleIvd=6236945
WaitersLists:
Lock=0x80cdf218[0x62d12800,0x62d12800]
Pin=0x80cdf1f8[0x80cdf1f8,0x80cdf1f8]
LoadLock=0x80cdf270[0x80cdf270,0x80cdf270]
Timestamp: Current=06-21-2026 08:37:46
HandleReference: Address=0x80cdf358 Handle=0xa0670d70 Flags=OWN[200]
LibraryObject: Address=0x903c0190 HeapMask=0000-0015-0015-0000 Flags=EXS/LOC[0004] Flags2=/CSC[4000000] Flags3=[0000] PublicFlags=NST[0001]
DataBlocks:
Block: #='0' name=KGLH0^8e860340 pins=0 Change=NONE
Heap=0x80cdefc8 Pointer=0x903c0270 Extent=0x903c00d0 Flags=I/-/P/A/-/-/-
FreedLocation=0 Alloc=2.179688 Size=3.976562 LoadTime=06-21-2026 06:38:20
Block: #='2' name=PLDIA^8e860340 pins=1 Change=NONE
Heap=0x903c0620 Pointer=0x80763550 Extent=0x80763490 Flags=I/-/P/A/-/-/-
FreedLocation=0 Alloc=13.593750 Size=15.820312 LoadTime=06-21-2026 06:38:20
Block: #='4' name=PLMCD^8e860340 pins=1 Change=NONE
Heap=0x903c0778 Pointer=0x955b7e68 Extent=0x955b7da8 Flags=I/-/P/A/-/-/-
FreedLocation=0 Alloc=2.554688 Size=3.937500 LoadTime=06-21-2026 06:38:20
--------------------------------------------------------
This lock request was aborted.
