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: ORA-600 Deadlock Issues

Mercadante, Thomas F

2005-06-27

Replies:
Karthik,

As you have figured out, the largest cause of Deadlock problems are bad
coding and missing indexes to support foreign keys. Below is a sql to
help you find tables with foreign keys but no indexes to support them.
The query is not 100% bullet proof, but it is a start.

I use this for Curam applications as they do not believe in supplying
indexes for foreign keys (their official response is to drop the FK's -
nice, eh?).

Good Luck!

SELECT 'create index ' || substr(ut.table_name,1,20) ||
ROUND(sys.dbms_random.value*100)||'IDX ' ||
'ON ' || ut.table_name || ' (' || ucc.column_name || ') ' ||
'TABLESPACE ' || ut.TABLESPACE_NAME||'PK pctfree 10' ddl_string
FROM USER_TABLES ut, USER_CONSTRAINTS uc, USER_CONS_COLUMNS ucc
WHERE uc.constraint_type='R'
AND ucc.constraint_name = uc.constraint_name
AND ut.table_name = uc.table_name
AND NVL(position,1) = 1
AND NOT EXISTS(SELECT 1 FROM USER_IND_COLUMNS uic
WHERE uic.table_name=ucc.table_name
AND  ucc.column_name = uic.column_name
AND  uic.column_position=1)
ORDER BY 1;

Tom

-----Original Message-----
From: oracle-l-bounce@(protected)
[mailto:oracle-l-bounce@(protected)
Sent: Monday, June 27, 2005 4:12 AM
To: oracle-l@(protected)
Subject: Re: ORA-600 Deadlock Issues

The issue turned out to be a very interesting one.

try this.

open two db sessions.

(session 1)
create table p( x number primary key);
create table c( x number references p);

insert into p values(1);
insert into c values(1);
insert into p values (2);
insert into c values(2);
insert into p values(3);
insert into c values(3);
commit;

(session 1)
update p set x = 2 where x = 2;

(session 2)
update p set x = 1 where x = 1;

(session 1) -- this will hang
update c set x = 2 where x = 2;

(session 2)
update c set x = 1 where x = 1;

you will find session 1 comes out with deadlock detected error.

now

create index c_idx on c(x);

try the same test case and the hang will be gone.

look up chapter 7 of tom kytes' book - Indexes on Foreign Keys. (page
142 in my book).

"The number one cause of deadlocks in the Oracle database, in my
experience, is unindexed foreign keys" - says the Guru himself.

Thanks, Karthik

On 25-Jun-05, at 9:42 PM, Mladen Gogala wrote:

>
> On 06/25/2005 03:04:25 AM, Egor Starostin wrote:
>
>> Are you sure that your first session doesn't receive ORA-60?
>> Probably, you just don't look in first session output.
>> I reproduced you case and ORA-60 was generated (which is expected).
>>
>> Note that hanganalyze in your case shows lock, not a deadlock.
>> Deadlocks are printed in 'Cycles' section of hanganalyze trace file.
>> Sessions from 'Open Cahins' section are just locks.
>
> That is correct, my first session did receive ora-0060 but I wasn't
> able to
> type fast enough. Yes, when I come to think of it, oracle will break
> the
> chain as soon as it discovers it. Hanganalyze will not have time
enough
> to catch it.
> --
> Mladen Gogala
> Oracle DBA
>
>
> --
> http://www.freelists.org/webpage/oracle-l
>

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