Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Oracle 9i , how do I find the deadlock object in the trace file?

Posted on 2003-12-10
3
2,021 Views
Last Modified: 2013-12-11
I have many UNIX servers with many Oracle instances. When a certain application is run on the database server, it creates a deadlock, which in turn hits a nasty Oracle 9.2.3 bug which corrupts Oracle Home and takes out all the Oracle instances on that server. I have lost 20 Oracle instances on 4 UNIX (HP-UX 9000 11.0 & 11.11) ( AIX 5.1) database servers. I need to find out what ojbect is causing the deadlock, so that I can have the developers fix the issue. How do I read the trace file, to find the deadlock table or object?  Following is a portion of one of the trace files.

 With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.3.0 - Production
ORACLE_HOME = /oracle/app/oracle/product/920
System name:    HP-UX
Node name:      dbsrv2
Release:        B.11.00
Version:        U
Machine:        9000/800
Instance name: lsu9
Redo thread mounted by this instance: 1
Oracle process number: 12
Unix process pid: 16414, image: oracle@dbsrv2 (TNS V1-V3)
*** 2003-11-08 14:57:33.744
*** SESSION ID:(18.437) 2003-11-08 14:57:33.700
DEADLOCK DETECTED
Current SQL statement for this session:
delete from dependency$ where d_obj#=:1
The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:
Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TX-000a001a-000075a0        12      18     X             12      18           X
session 18: DID 0001-000C-00000002      session 18: DID 0001-000C-00000002
Rows waited on:
Session 18: no row
Information on the OTHER waiting sessions:
End of information on OTHER waiting sessions.
===================================================
PROCESS STATE
-------------
Process global information:
     process: c0000000094581f0, call: c0000000095986a8, xact: c00000000a3c7850,
curses: c0000000094bfb50, usrses: c0000000094c4540
  ----------------------------------------
  SO: c0000000094581f0, type: 2, owner: 0000000000000000, flag: INIT/-/-/0x00
  (process) Oracle pid=12, calls cur/top: c0000000095986a8/c000000009598468, fla
g: (0) -
            int error: 0, call error: 0, sess error: 0, txn error 0
  (post info) last post received: 0 0 0
              last post received-location: No post
              last process to post me: none
Standard input
0
Comment
Question by:bonniekittelson
  • 2
3 Comments
 
LVL 7

Accepted Solution

by:
yoren earned 250 total points
ID: 9982799
Since there's no particular row that's getting locked, you may be getting deadlocks due to insufficient transaction slots. Try increasing the INITRANS storage parameter on your tables. As far as determining what table is missing, try adding the following line to your init.ora and see if you get anything more.

event='60 trace name errorstack level 10'

(You can find more on this at http://www.orafaq.com/faqdbain.htm)
0
 
LVL 7

Expert Comment

by:yoren
ID: 9982802
Correction - I meant "determining what table is deadlocking" instead of "determining what table is missing"
0
 

Author Comment

by:bonniekittelson
ID: 10012825
I will put the trace in and see if I can get any more information on this error. I will let you know.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Oracle query output question 4 47
Shredding xml into an oracle 11g Database 2 58
Creation date for a PDB 5 39
oracle differnce between two timestamps 5 31
This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question