Note: Tested in Oracle 19c
Open two Sqplus sessions: SESS-1 and SESS-2.
1. At T1, login to SESS-1, register two alert interests: alert_1 and alert_2, then exit the session:
exec dbms_alert.register('alert_1');
exec dbms_alert.register('alert_2');
exit;
2. At T2, re-login to SESS-1 (sid: 552), check two registered names:
-- watch two registered names, whose session is not alive.
select * from sys.dbms_alert_info where name like 'ALERT%';
NAME SID CHANGED MESSAGE
--------------- --------------- ---------- ---------------
ALERT_1 0228568D0001 N
ALERT_2 0228568D0001 N
-- determines if the specified session is active.
declare
l_unique_sid varchar2(30) := '0228CD530001';
begin
if not dbms_session.is_session_alive(l_unique_sid) then
dbms_output.put_line('Not Alive: (sid: ' ||to_number(substr(l_unique_sid, 1, 4), 'XXXX')||
', serial#: '||to_number(substr(l_unique_sid, 5, 4), 'XXXX')||
', inst: '||to_number(substr(l_unique_sid, 9, 4), 'XXXX')||')');
end if;
end;
/
Not Alive: (sid: 552, serial#: 52563, inst: 1)
3. At T3, continue in SESS-1, send signal: alert_2
SQL (sid:552) > exec dbms_alert.signal('alert_2', 'alert_msg_2');
4. At T4, login to SESS-2 (sid: 16), register one alert interests: alert_3
SQL (sid:16) > exec dbms_alert.register('alert_3');
--Note that this call is with default cleanup = true to perform cleanup of any extant orphaned pipes.
5. At T5, in SESS-1, check TX lock, SESS-2 (sid:16) is blocked by SESS-1 (sid: 552)
SQL (sid:552) > select * from v$lock where type = 'TX';
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK CON_ID
---------------- ---------------- ---- -- ---------- ---------- ---------- ---------- ---------- ---------- ----------
00000000B4310970 00000000B43109A0 16 TX 5505024 782554 0 6 132 0 0
00000000ADB30E70 00000000ADB30EA8 552 TX 5505024 782554 6 0 169 1 0
00000000ADBCFAB8 00000000ADBCFAF0 16 TX 6291482 236621 6 0 132 0 0
3 rows selected.
6. At T6, in SESS-1, send signal: alert_1
SQL (sid:552) > exec dbms_alert.signal('alert_1', 'alert_msg_1');
7. Both sessions throw ORA-00060
SQL (sid:552) > exec dbms_alert.signal('alert_1', 'alert_msg_1');
BEGIN dbms_alert.signal('alert_1', 'alert_msg_1'); END;
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at "SYS.DBMS_ALERT", line 431
ORA-06512: at line 1
SQL (sid:16) > exec dbms_alert.register('alert_3');
BEGIN dbms_alert.register('alert_3'); END;
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at "SYS.DBMS_ALERT", line 82
ORA-06512: at "SYS.DBMS_ALERT", line 82
ORA-06512: at "SYS.DBMS_ALERT", line 103
ORA-06512: at line 1
8. Both Deadlock trc files:
=================== testdb_ora_1851016.trc ===================
Unix process pid: 1851016, image: oracle@testdb
*** SESSION ID:(552.57779) 2026-06-09T15:35:50.836313+02:00
Deadlock graph:
------------Blocker(s)----------- ------------Waiter(s)------------
Resource Name process session holds waits serial process session holds waits serial
TX-00540000-000BF0DA-00000000-00000000 63 552 X 57779 61 196 X 9237
TX-005B0001-000BE24C-00000000-00000000 61 196 X 9237 63 552 X 57779
----- Information for waiting sessions -----
Session 552:
Holds resource TX-00540000-000BF0DA-00000000-00000000 acquired 245 seconds ago.
sid: 552 ser: 57779 audsid: 131915017 user: 0/SYS
flags: (0x41) USR/- flags2: (0x40009) -/-/INC
flags_idl: (0x1) status: BSY/-/-/- kill: -/-/-/-
pid: 63 O/S info: user: oracle, term: UNKNOWN, ospid: 1851016
image: oracle@testdb
client details:
O/S info: user: U000380, term: WCLINBDOXKKDUGS, ospid: 57408:55064
machine: SYS\WCLINBDOXKKDUGS program: sqlplus.exe
application name: SQL*Plus, hash value=3669949024
current SQL:
UPDATE DBMS_ALERT_INFO SET CHANGED = 'Y', MESSAGE = :B2 WHERE NAME = UPPER(:B1 )
Session 196:
Holds resource TX-005B0001-000BE24C-00000000-00000000 acquired 3 seconds ago.
sid: 196 ser: 9237 audsid: 131915018 user: 0/SYS
flags: (0x10041) USR/- flags2: (0x40009) -/-/INC
flags_idl: (0x1) status: BSY/-/-/- kill: -/-/-/-
pid: 61 O/S info: user: oracle, term: UNKNOWN, ospid: 1850047
image: oracle@testdb (J006)
client details:
O/S info: user: oracle, term: UNKNOWN, ospid: 1850047
machine: testdb program: oracle@testdb (J006)
application name: task-911-2 hash value=1117638710
action name: bgp: loop: init, hash value=2828460256
current SQL:
DELETE DBMS_ALERT_INFO WHERE SID = :B1
session 552: DID 0001-003F-000005CF session 196: DID 0001-003D-000004C1
session 196: DID 0001-003D-000004C1 session 552: DID 0001-003F-000005CF
Rows waited on:
Session 552: obj - rowid = 003C067C - AAPAZ8AG+AAAXuhAAB
(dictionary objn - 3933820, file - 446, block - 97185, slot - 1)
Session 196: obj - rowid = 003C067C - AAPAZ8AG+AAAXuhAAC
(dictionary objn - 3933820, file - 446, block - 97185, slot - 2)
=================== testdb_ora_1851008.trc ===================
Unix process pid: 1851008, image: oracle@testdb
*** SESSION ID:(16.33150) 2026-06-09T15:35:47.444358+02:00
Deadlock graph:
------------Blocker(s)----------- ------------Waiter(s)------------
Resource Name process session holds waits serial process session holds waits serial
TX-0060001A-00039C4D-00000000-00000000 66 16 X 33150 63 552 X 57779
TX-00540000-000BF0DA-00000000-00000000 63 552 X 57779 66 16 X 33150
----- Information for waiting sessions -----
Session 16:
Holds resource TX-0060001A-00039C4D-00000000-00000000 acquired 204 seconds ago.
sid: 16 ser: 33150 audsid: 131915016 user: 0/SYS
flags: (0x41) USR/- flags2: (0x40009) -/-/INC
flags_idl: (0x1) status: BSY/-/-/- kill: -/-/-/-
pid: 66 O/S info: user: oracle, term: UNKNOWN, ospid: 1851008
image: oracle@testdb
client details:
O/S info: user: U000380, term: WCLINBDOXKKDUGS, ospid: 42636:42136
machine: SYS\WCLINBDOXKKDUGS program: sqlplus.exe
application name: SQL*Plus, hash value=3669949024
current SQL:
DELETE DBMS_ALERT_INFO WHERE SID = :B1
Session 552:
Holds resource TX-00540000-000BF0DA-00000000-00000000 acquired 241 seconds ago.
sid: 552 ser: 57779 audsid: 131915017 user: 0/SYS
flags: (0x41) USR/- flags2: (0x40009) -/-/INC
flags_idl: (0x1) status: BSY/-/-/- kill: -/-/-/-
pid: 63 O/S info: user: oracle, term: UNKNOWN, ospid: 1851016
image: oracle@testdb
client details:
O/S info: user: U000380, term: WCLINBDOXKKDUGS, ospid: 57408:55064
machine: SYS\WCLINBDOXKKDUGS program: sqlplus.exe
application name: SQL*Plus, hash value=3669949024
current SQL:
UPDATE DBMS_ALERT_INFO SET CHANGED = 'Y', MESSAGE = :B2 WHERE NAME = UPPER(:B1 )
session 16: DID 0001-0042-0000011E session 552: DID 0001-003F-000005CF
session 552: DID 0001-003F-000005CF session 16: DID 0001-0042-0000011E
Rows waited on:
Session 16: obj - rowid = 003C067C - AAPAZ8AG+AAAXuhAAC
(dictionary objn - 3933820, file - 446, block - 97185, slot - 2)
Session 552: obj - rowid = 003C067C - AAPAZ8AG+AAAXuhAAB
(dictionary objn - 3933820, file - 446, block - 97185, slot - 1)
9. Repeat Test
To repeat test, exit both SESS-1 and SESS-2, open a third SESS-3, and register a new alert to cleanup alert_1, alert_2, alert_3. Then repeat above test steps.
exec dbms_alert.register('alert_other');
