Sandy, to add to what Rick's reply, the "SQL*Net message from client" indicates
to me the user made an update and failed to commit it. I think Rick provided
valid possibilities for EM but for why you could not directly query the
information I think we would need to see what queries you used to try to find
the blocker though from your final remarks you did use GV$LOCK and GV$SESSION
plus sys.dbms_lock_allocated to find the blocker. Could the earlier attempt
have been using the V$ version and so missed the blocker since it was on
another instance? The sys.dbms_lock_allocated table would not be necessary to
find the blocking session though it would identify which User Lock (UL) was
involved if a UL was involved.
Mark Powell
Database Administration
(313) 592-5148
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Sandra Becker <sbecker6925@xxxxxxxxx>
Sent: Wednesday, February 21, 2018 10:32:14 AM
To: oracle-l
Subject: Unable to detect blocking session
Oracle EE 12.1.0.2 on RHEL 5.11
We had a situation in our production environment yesterday where a user had a
sqlplus session had an uncommitted "zero row" update on a table. This kept the
actual application from processing orders using that same table. The sqlplus
session was initiated from SQL*Plus Release 11.1.0.6.0. The wait event on the
application session was holding a user lock, which was apparently blocked, with
a wait event of "SQL*Net message from client". Once the user's sqlplus
session was exited, all application sessions resumed normal functions without
any intervention. The user who issues the update is a tier1 support person, so
we can't lock out their access for such activites to prevent future occurrences.
What we are trying to understand is why we were unable to see that the user's
sqlplus session was blocking either through EM or through queries looking at
gv$session and gv$session_blockers. We found the application locks by joining
gv$lock, gv$session and sys.dbms_lock_allocated. Any ideas why we could see
the blocking or suggestions on what we can look at so we don't miss it again?
--
Sandy B.
