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: Unindexed FK Cause Deadlock or Only Share Lock?

Jonathan Lewis



Your question is a good question, and one which
I have been meaning to test for the last five years
or so.

Because an index entry MUST go in the right block,
Oracle can check for the existence of child rows of
the parent you are deleting (or updating) by looking
at the right block of the child index - if it has no
related child rows then your session can pin it in
exclusive mode in the buffer whilst modifying the
parent row. If it has child rows then your parent
update/delete must either wait or fail - depending on
whether or not the child row is an uncommitted row
from another transaction The answer to your specific
question about an index when there are only two
values with thousands of rows is therefore - Oracle
will find very rapidly that it can't change the parent,
so the number of rows for a value is irrelevant.
It's just bad luck that the index is otherwise inherently
a useless index. (But it sounds like a parent that
you shouldn't be able to update, so you could
try to disable table locks on the child to bypass
the issue).

But the thing I haven't tested is what happens if
there used to be thousands of leaf blocks that
ONCE HELD entries for a given value which have
been deleted but not cleaned out. It seems that your
session might have to pin thousands of blocks
in the buffer (to discover that there are no child
rows still in those thousands of blocks) prior to
modifying the parent - and that's not possible.


Jonathan Lewis

The Co-operative Oracle Users' FAQ

Public Appearances - schedule updated June 22nd 2005

----- Original Message -----
From: "Post, Ethan" <Ethan.Post@(protected)>
To: <jonathan@(protected))"
Sent: Thursday, June 30, 2005 9:36 PM
Subject: RE: Unindexed FK Cause Deadlock or Only Share Lock?

I was hoping one of the more brilliant scienticians from the other side
of the pond would chime in. If I may beg you to waste a little more time
with my humble request. What happens when there is an index, and let's
assume the index only contains 2 distinct values over zillions of
records. Have we eliminated the deadlock scenario below entirely? I am
all for indexing my FK's but was not aware such a otherwise useless
index (as described in previous sentence) could be so useful?