OraFAQ Forum: Server Administration

Home » RDBMS Server » Server Administration » ORA-60

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-60 [message #56309]

Tue, 18 March 2003 08:56

reshma
Messages: 10
Registered: January 2003

Junior Member

Hi,
I would appreciate if anybody can explain me What to do with "ORA-60 Deadlock Detected" Errors.

Thanks
Reshma

Report message to a moderator

Re: ORA-60 [message #56310 is a reply to message #56309]

Tue, 18 March 2003 09:47

psmyth
Messages: 81
Registered: October 2002

Member

try running the script below... it will show which users are holding the locks ( deadlock being where two users are holding locks on rows that each other need to use - neither can get access to the rows it needs as they are locked by the other user).

The solution is to kill one of the users and the other will continue once the killed user has rolled back.

script... run it as a 'dba' user or a user that has access to the v$views
===================================================
--------------------------------------------------------------------------------
-- Filename: locktree.sql
--
-- Purpose: Show locks and lock dependencies for active sessions.
--
-- Parameter: None
--
-- Author: Udo Basche
-- Date: 09-MAR-2000
--------------------------------------------------------------------------------

set lines 132 pages 24

rem column machine heading 'Machine'
column machine noprint
column username heading 'Username'
column pid format 999 heading 'Pid'
column term format a10 heading 'Term'
column lock_type format a18 heading 'Lock type'
column obj_name format a30 heading 'Object Name'
column obj_type format a8 heading 'Obj Type'
column sid format 9999 heading 'Sid'
column serial format 999999 heading 'Ser#'
column spid format 99999999 heading 'Unix ID'
column wait format a10 heading 'Wait?'

SELECT
substr(sess.username,1,9) username,
substr(sess.machine,1,5) machine,
proc.pid pid,
proc.spid spid,
sess.terminal term,
decode(lck.type, 'RW','Rw wt enq','TM','DML enq',
'TX','Trans enq','UL','Usr sppld',
lck.type)||'('||
decode(lck.lmode,1,'Null', 2,'Row shr', 3,'Row excl',
4,'Shr', 5,'Shr row excl', 6,'Excl')||')' lock_type,
substr(decode(obj$.name,null,'?',obj$.name),1,30) obj_name,
substr(decode(obj$.type#, 0, 'NEXT OBJ', 1, 'INDEX ', 2, 'TABLE ',
3, 'CLUSTER ', 4, 'VIEW ', 5, 'SYNONYM ',
6, 'SEQUENCE', 7, 'PROC ', 8, 'FUNCTION',
9, 'PACKAGE ', 11, 'PKG BODY', 12, 'DATE ',
23, 'RAW ', 24, 'LONG RAW', 69, 'ROWID ',
96, 'CHAR ',105, 'MLSLABEL',106, 'MLSLABEL',
'? '),1,8) obj_type,
sess.sid sid,
sess.serial# serial,
decode(lck.request,0,'Owner','...Waiter') wait
FROM
v$lock lck,
v$session sess,
sys.obj$,
v$process proc
WHERE
lck.sid = sess.sid AND
obj$.obj#(+) = lck.id1 AND
proc.addr = sess.paddr AND sess.username is not null AND
lck.id1||lck.type IN
(SELECT lck1.id1 || lck1.type
FROM v$lock lck1
WHERE lck1.request <> 0)
ORDER BY
obj_name,
wait desc
/
===================================================

Report message to a moderator

Previous Topic:	Oracle installation problem
Next Topic:	Executing Host Commands via Java Stored Procedure in a DB job throws up defunct proceses

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 20 00:40:18 CDT 2024