Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> comp.databases.oracle.misc,comp.databases.oracle.server,comp.databases.oracle.tools

comp.databases.oracle.misc,comp.databases.oracle.server,comp.databases.oracle.tools

From: <yong321_at_yahoo.com>
Date: 2000/05/29
Message-ID: <8gsqvd$n50$1@nnrp1.deja.com>#1/1

In article <LllY4.3907$N4.134036_at_ozemail.com.au>,   "Rolf" <rolfhr_at_ozemail.com.au> wrote:
> select B.SID, C.USERNAME, C.OSUSER, C.TERMINAL,
> DECODE(B.ID2, 0, A.OBJECT_NAME,
> 'Trans-'||to_char(B.ID1)) OBJECT_NAME,
> B.TYPE,
> DECODE(B.LMODE,0,'--Waiting--',
> 1,'Null',
> 2,'Row Share',
> 3,'Row Excl',
> 4,'Share',
> 5,'Sha Row Exc',
> 6,'Exclusive',
> 'Other') "Lock Mode",
> DECODE(B.REQUEST,0,' ',
> 1,'Null',
> 2,'Row Share',
> 3,'Row Excl',
> 4,'Share',
> 5,'Sha Row Exc',
> 6,'Exclusive',
> 'Other') "Req Mode"
> from DBA_OBJECTS A, V$LOCK B, V$SESSION C
> where A.OBJECT_ID(+) = B.ID1
> and B.SID = C.SID
> and C.USERNAME is not null
> order by B.SID, B.ID2;

The original poster may like to select from v$locked_object better. From this and dba_objects joining on object_id you can find the object. To kill a session, alter system kill session '[sid],[serial#]' where sid is from v$locked_object and serial# is from v$session (together with sid). Don't just kill the process at the OS level.

The reason your Oracle instance doesn't handle locks correctly may be due to a bug, unless you see something obvious in your alert log. Call Oracle Support to see if there's a patch. In any case, upgrading your NT Service Pack should be done because you're still using SP3.

Yong Huang

(yong321_at_yahoo.com)
(http://www.stormloader.com/yonghuang/)

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon May 29 2000 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US