Home
All Oracle Error Codes
Oracle DBA Forum

Frequent Oracle Errors

TNS:could not resolve the connect identifier specified
Backtrace message unwound by exceptions
invalid identifier
PL/SQL compilation error
internal error
missing expression
table or view does not exist
end-of-file on communication channel
TNS:listener unknown in connect descriptor
insufficient privileges
PL/SQL: numeric or value error string
TNS:protocol adapter error
ORACLE not available
target host or object does not exist
invalid number
unable to allocate string bytes of shared memory
resource busy and acquire with NOWAIT specified
error occurred at recursive SQL level string
ORACLE initialization or shutdown in progress
archiver error. Connect internal only, until freed
snapshot too old
unable to extend temp segment by string in tablespace
Credential retrieval failed
missing or invalid option
invalid username/password; logon denied
unable to create INITIAL extent for segment
out of process memory when trying to allocate string bytes
shared memory realm does not exist
cannot insert NULL
TNS:unable to connect to destination
remote database not found ora-02019
exception encountered: core dump
inconsistent datatypes
no data found
TNS:operation timed out
PL/SQL: could not find program
existing state of packages has been discarded
maximum number of processes exceeded
error signaled in parallel query server
ORACLE instance terminated. Disconnection forced
TNS:packet writer failure
see ORA-12699
missing right parenthesis
name is already used by an existing object
cannot identify/lock data file
invalid file operation
quoted string not properly terminated

RE: Deadlock problem

Alessandro Vercelli

2006-05-10

Replies:
Sorry for the wrong trace file: here is the correct one:

*** SESSION ID:(29.650) 2006.05.10.03.58.18.000
A deadlock among DDL and parse locks is detected.
This deadlock is usually due to user errors in
the design of an application or from issuing a set
of concurrent statements which can cause a deadlock.
This should not be reported to Oracle Support.
The following information may aid in finding
the errors which cause the deadlock:
ORA-04020: deadlock detected while trying to lock object SYSUTIL_OWNER.ADFDATASETDESCRIPTOR
--------------------------------------------------------
object  waiting waiting     blocking blocking
handle  session   lock mode  session   lock mode
-------- -------- -------- ---- -------- -------- ----
9bd59b80 9c774d98 9e0422bc   X 9c774d98 9e042e74   S
--------------------------------------------------------
---------- DUMP OF WAITING AND BLOCKING LOCKS ----------
--------------------------------------------------------
------------- WAITING LOCK -------------
----------------------------------------
SO: 9e0422bc, type: 23, owner: 9dfbe6b8, flag: INIT/-/-/0x00
LIBRARY OBJECT LOCK: lock=9e0422bc handle=9bd59b80 request=X
call pin=0 session pin=0
user=9c774d98 session=9c774d98 count=0 flags=[00] savepoint=17051
LIBRARY OBJECT HANDLE: handle=9bd59b80
name=SYSUTIL_OWNER.ADFDATASETDESCRIPTOR
hash=c41c8eb9 timestamp=06-16-2003 08:48:33
namespace=TABL/PRCD/TYPE flags=TIM/SML/[02000000]
kkkk-dddd-llll=0000-050d-050d lock=S pin=S latch=3
lwt=9bd59b98[9e0422cc,9e0422cc] ltm=9bd59ba0[9bd59ba0,9bd59ba0]
pwt=9bd59bb0[9bd59bb0,9bd59bb0] ptm=9bd59c08[9bd59c08,9bd59c08]
ref=9bd59b88[9a25b544,9bd5b670]
LIBRARY OBJECT: object=9bd22008
type=TABL flags=EXS/LOC[0005] pflags= [00] status=VALD load=0
DATA BLOCKS:
data#   heap pointer status pins change
----- -------- -------- ------ ---- ------
   0 9ba8e9b4 9bd2214c I/P/A   0 NONE
   2 9bd22198 9a251648 I/P/A   1 NONE
   3 9a2553e8 9a254fc8 I/-/A   0 NONE
   8 9bd2209c 9bb43f78 I/-/A   0 NONE
  10 9bd220fc 9bb3ebec I/-/A   0 NONE
------------- BLOCKING LOCK ------------
----------------------------------------
SO: 9e042e74, type: 23, owner: 9c7da4d0, flag: INIT/-/-/0x00
LIBRARY OBJECT LOCK: lock=9e042e74 handle=9bd59b80 mode=S
call pin=9e036e90 session pin=0
user=9c774d98 session=9c774d98 count=2 flags=PNC/[04] savepoint=248
LIBRARY OBJECT HANDLE: handle=9bd59b80
name=SYSUTIL_OWNER.ADFDATASETDESCRIPTOR
hash=c41c8eb9 timestamp=06-16-2003 08:48:33
namespace=TABL/PRCD/TYPE flags=TIM/SML/[02000000]
kkkk-dddd-llll=0000-050d-050d lock=S pin=S latch=3
lwt=9bd59b98[9e0422cc,9e0422cc] ltm=9bd59ba0[9bd59ba0,9bd59ba0]
pwt=9bd59bb0[9bd59bb0,9bd59bb0] ptm=9bd59c08[9bd59c08,9bd59c08]
ref=9bd59b88[9a25b544,9bd5b670]
LIBRARY OBJECT: object=9bd22008
type=TABL flags=EXS/LOC[0005] pflags= [00] status=VALD load=0
DATA BLOCKS:
data#   heap pointer status pins change
----- -------- -------- ------ ---- ------
   0 9ba8e9b4 9bd2214c I/P/A   0 NONE
   2 9bd22198 9a251648 I/P/A   1 NONE
   3 9a2553e8 9a254fc8 I/-/A   0 NONE
   8 9bd2209c 9bb43f78 I/-/A   0 NONE
  10 9bd220fc 9bb3ebec I/-/A   0 NONE
--------------------------------------------------------
This lock request was aborted.

The indexes are periodically rebuilded.

Thanks for your help,

Alessandro


> Hi Alessandro,
>
> First off, this is an ORA-0060, not an ORA-4020. (ORA-60 is an enqueue
> deadlock, ORA-4020 is a library cache deadlock.)
>
> Second, it's a TX (transaction enqueue) deadlock.
>
> Third, the mode held by the blocker and the mode held by the waiter are
> 'X' (exclusive).
>
> Fourth, the statement encountering the deadlock is an UPDATE.
>
> So, the combination of the above info tells me that this is a row-level
> application deadlock. You've got two concurrent sessions, one session
> updates row X and does not commit. Another session updates row Y and
> does not commit. Then, the first session tries updating row Y and
> starts waiting on the second session, and finally, the second session
> tries to update row X and starts waiting on the first session. In this
> state, the sessions would wait forever, so, Oracle detects a deadlock,
> raises ORA-0060, and statement level rollback occurs.
>
> I don't know anything about your application, however, the cleanest
> solution would be to examine the application logic, and alter as
> necessary to ensure that two concurrent sessions do not attempt to
> update the same set of rows. Also, another possiblility, which would
> still suffer from slowdowns due to TX enqueue waits, but not deadlocks,
> would be to ensure that the order that rows are updated is the same in
> all the concurrent sessions.
>
> Hope that helps,
>
> -Mark
>
>
>
>
> --
> Mark J. Bobak
> Senior Oracle Architect
> ProQuest Information & Learning
>
> For a successful technology, reality must take precedence over public
> relations, for Nature cannot be fooled. --Richard P. Feynman, 1918-1988
>
>
> -----Original Message-----
> From: oracle-l-bounce@(protected)
> [mailto:oracle-l-bounce@(protected)
> Sent: Monday, May 08, 2006 1:34 PM
> To: Oracle Freelists.org
> Subject: [SPAM] Deadlock problem
> Importance: Low
>
> Hi all,
> I'm trying to solve an ora-4020 (deadlock) issue; the trace file (sorry
> if messed) shows:
>
> ksqded1: deadlock detected via did
> DEADLOCK DETECTED
> Current SQL statement for this session:
> Update <TABLE> set <FIELD1>='<VALUE>' where <FIELD2> like '<STRING>%'
> The following deadlock is not an ORACLE error. It is a deadlock due to

--
http://www.freelists.org/webpage/oracle-l