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.
