| |
 |
|
Oracle Tips by Burleson |
OCP Instructors Guide for
Oracle DBA Certification
Chapter 8 -
Miscellaneous Oracle Information
Deadlock Detection
Transaction deadlocks occur when two or more
transactions are attempting to access an object with incompatible
lock modes. The following script can be used to identify deadlocks
in the database. The query depends upon objects that are created by
the script $ORACLE_HOME/rdbms/admin/dbmslock.sql. Log on as SYS or
with SYSDBA authority and run this script in all databases. You may
have to run the deadlock monitoring script below numerous times
before you identify the transaction that is causing the problem. A
neat trick is to embed the script in a PL/SQL stored procedure that
accepts command-line input parameters for the total number of runs
and time intervals between runs. The output can be sent to a table
or to an output datafile.
select
dl.session_id, s.serial#, substr(dl.mode_held,1,4), s.osuser,
o.owner,o.object_name, o.object_type, l.type from dba_objects o,
v$locked_object lo, v$lock l,dba_locks dl, v$session s
SEE
CODE DEPOT FOR FULL SCRIPT
The above text is
an excerpt from:
OCP Instructors Guide for Oracle DBA Certification
A Study Guide to Advanced Oracle Certified Professional Database
Administration Techniques
ISBN 0-9744355-3-8
by Christopher T. Foot
http://www.rampant-books.com/book_2003_2_OCP_print.htm
 |
For more details and scripts, see my new book "
Oracle
Tuning: The Definitive Reference", over 900 pages
of BC's favorite tuning tips & scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot. |
|
