Databases Reference
In-Depth Information
We can acquire data on locking from the
V$SESSION
dynamic performance view also, as in
step 12. Here we join the
V$DATAFILE
and
ALL_OBJECTS
views to see the data files and
objects responsible for the lock. In the following screenshot we can see the results obtained
by executing this query:
When we are in situations like the one analyzed in this recipe we can solve the issue by killing
the blocked or the blocking session. To do so we need to know the
SID
and the
Serial#
of
the session to kill, so in step 13 we query for this data from
V$SESSION
and in step 14 we
kill the blocked session (
SESSION
2
). We can see the results of these operations in the next
screenshot, where on the left side you will see the session in which we are connected as
SYSDBA
and on the right side you can see the output of the killed session:
In the above screenshot you can see that, in the killed session, an error message informs
us about what happened.
Another way to solve this issue is to terminate the blocking transactions by executing a
COMMIT
or a
ROLLBACK
. In step 15 we execute the
ROLLBACK
of the update, terminating
the transaction and leaving the data in the table unchanged.
