Raj, Are you certain that in all the cases id1 is an obj#? Its meaning depends on the type of lock. An unfortunate coincidence might make it match an obj# when it is actually something else.
HTH SF >----- ------- Original Message ------- ----- >From: "Jamadagni, Rajendra" ><[EMAIL PROTECTED]> >To: Multiple recipients of list ORACLE-L ><[EMAIL PROTECTED]> >Sent: Mon, 06 Oct 2003 07:29:30 > >------_=_NextPart_001_01C38C16.3B1E156B >Content-Type: text/plain; > charset="iso-8859-1" > >Hi all, > >I am using following script to see locks in the DB. >Do you see any problems >with it? > >======================== cut here >===================== >connect / as sysdba >set linesize 200 feedback off heading on pagesize >100 >column sid format a9 >column res heading 'Resource Type' format a15 trunc > >column id1 format 9999999 noprint >column id2 format 9999999 noprint >column lmode heading 'Lock Held' format a14 >column request heading 'Lock Req.' format a14 >column serial# format 99999 >column username format a10 >column terminal heading Term format a8 >column table_name format a20 trunc >column owner format a10 >column inst_id format a5 >select --+ no_merge(l) no_merge(s) > (select instance_name > from sys.gv_$instance > where instance_number = l.inst_id) >inst_id, > l.sid || ',' || s.serial# sid, > s.username, > replace(s.terminal,'WTS-') terminal, > decode(l.type, > 'RW','RW-Row Wait Enq', > 'TM','TM-DML Enq', > 'TX','TX-Trans Enq', > 'UL','UL-User',l.type||'-System') >res, > t.name table_name,u.name owner, > l.id1,l.id2, > decode(l.lmode,1,'No Lock', > 2,'Row Share', > 3,'Row Excl', > 4,'Share', > 5,'Shr Row Excl', > 6,'Excl',null) lmode, > decode(l.request,1,'No Lock', > 2,'Row Share', > 3,'Row Excl', > 4,'Share', > 5,'Shr Row Excl', > 6,'Excl',null) request >from sys.gv_$lock l, sys.gv_$session s, sys.user$ >u,sys.obj$ t >where l.sid = s.sid >and s.type != 'BACKGROUND' >and t.obj# = l.id1 >and u.user# = t.owner# >and l.inst_id = s.inst_id >/ >prompt >set feedback on >prompt >exit >======================== cut here >===================== > >Btu here is the problem ... once in a while (aka >many times a day) when we >run this script, we see objects as locked by some >user which should NEVER be >even accessed. We have one schema that deals with >out affiliates, and it is >practically independent of other schema in the >database. Still sometimes we >see objects within the affiliate schema beign >accesses by other users who >have nothing to do (or the code they execute has >nothing to do) with the >objects displayed in the list. > >One peculier thing I have noted, is affiliate >schema used private synonyms >and the objects listed in the lock scripts are >_always_ private synonyms >pointing to objects in the affiliate schema and the >private synonym belongs >to the user who is _not_ the locking user. > >e.g. > >ABC1 47,820 BROWNBRE BRS02 TX-Trans Enq >SYSTEM_NETWORK_HIST >MURPHYM Excl > >here locking user ius brownbre locking >system_network_hist owned by murphym. >Actually the table is owned by affiliate and >murphym has a private synonym >to the table. > >Any clues? DB is 9202 RAC. >TIA >Raj > >---- >Rajendra dot Jamadagni at nospamespn dot com >All Views expressed in this email are strictly >personal. >QOTD: Any clod can have facts, having an opinion is >an art ! > > >------_=_NextPart_001_01C38C16.3B1E156B >Content-Type: text/html; > charset="iso-8859-1" >Content-Transfer-Encoding: quoted-printable > ><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"> ><HTML> ><HEAD> ><META HTTP-EQUIV=3D"Content-Type" >CONTENT=3D"text/html; = >charset=3Diso-8859-1"> ><META NAME=3D"Generator" CONTENT=3D"MS Exchange >Server version = >5.5.2654.45"> ><TITLE>Question with lock script - phantom >objects</TITLE> ></HEAD> ><BODY> > ><P><FONT SIZE=3D2 FACE=3D"Courier New">Hi >all,</FONT> ></P> > ><P><FONT SIZE=3D2 FACE=3D"Courier New">I am using >following script to = >see locks in the DB. Do you see any problems with >it? </FONT> ></P> > ><P><FONT SIZE=3D2 FACE=3D"Courier = >=3D cut here = ><BR><FONT SIZE=3D2 FACE=3D"Courier New">connect / >as sysdba</FONT> ><BR><FONT SIZE=3D2 FACE=3D"Courier New">set >linesize 200 feedback off = >heading on pagesize 100</FONT> ><BR><FONT SIZE=3D2 FACE=3D"Courier New">column sid >format a9</FONT> ><BR><FONT SIZE=3D2 FACE=3D"Courier New">column res >heading 'Resource = >Type' format a15 trunc</FONT> ><BR><FONT SIZE=3D2 FACE=3D"Courier New">column id1 >format 9999999 = >noprint</FONT> ><BR><FONT SIZE=3D2 FACE=3D"Courier New">column id2 >format 9999999 = >noprint</FONT> ><BR><FONT SIZE=3D2 FACE=3D"Courier New">column >lmode heading 'Lock = >Held' format a14</FONT> ><BR><FONT SIZE=3D2 FACE=3D"Courier New">column >request heading 'Lock = >Req.' format a14</FONT> ><BR><FONT SIZE=3D2 FACE=3D"Courier New">column >serial# format = >99999</FONT> ><BR><FONT SIZE=3D2 FACE=3D"Courier New">column >username format = >a10</FONT> ><BR><FONT SIZE=3D2 FACE=3D"Courier New">column >terminal heading Term = >format a8</FONT> ><BR><FONT SIZE=3D2 FACE=3D"Courier New">column >table_name format a20 = >trunc</FONT> ><BR><FONT SIZE=3D2 FACE=3D"Courier New">column >owner format a10</FONT> ><BR><FONT SIZE=3D2 FACE=3D"Courier New">column >inst_id format a5</FONT> ><BR><FONT SIZE=3D2 FACE=3D"Courier >New">select --+ no_merge(l) = >no_merge(s)</FONT> ><BR><FONT SIZE=3D2 FACE=3D"Courier = >New"> >(select instance_name = ></FONT> ><BR><FONT SIZE=3D2 FACE=3D"Courier = >sys.gv_$instance </FONT> ><BR><FONT SIZE=3D2 FACE=3D"Courier = >instance_number =3D l.inst_id) inst_id,</FONT> ><BR><FONT SIZE=3D2 FACE=3D"Courier = >New"> >l.sid || ',' || = >s.serial# sid,</FONT> ><BR><FONT SIZE=3D2 FACE=3D"Courier = >New"> >s.username,</FONT> ><BR><FONT SIZE=3D2 FACE=3D"Courier = >New"> = >replace(s.terminal,'WTS-') terminal,</FONT> ><BR><FONT SIZE=3D2 FACE=3D"Courier = >New"> >decode(l.type,</FONT> ><BR><FONT SIZE=3D2 FACE=3D"Courier = >nbsp; 'RW','RW-Row Wait Enq',</FONT> ><BR><FONT SIZE=3D2 FACE=3D"Courier = >nbsp; 'TM','TM-DML Enq',</FONT> ><BR><FONT SIZE=3D2 FACE=3D"Courier = >nbsp; 'TX','TX-Trans Enq',</FONT> ><BR><FONT SIZE=3D2 FACE=3D"Courier = >nbsp; 'UL','UL-User',l.type||'-System') >res,</FONT> ><BR><FONT SIZE=3D2 FACE=3D"Courier = >New"> >t.name = >table_name,u.name owner,</FONT> ><BR><FONT SIZE=3D2 FACE=3D"Courier = >New"> >l.id1,l.id2,</FONT> ><BR><FONT SIZE=3D2 FACE=3D"Courier = >New"> >decode(l.lmode,1,'No = >Lock',</FONT> ><BR><FONT SIZE=3D2 FACE=3D"Courier = >nbsp; 2,'Row Share',</FONT> ><BR><FONT SIZE=3D2 FACE=3D"Courier = >nbsp; 3,'Row Excl',</FONT> ><BR><FONT SIZE=3D2 FACE=3D"Courier = >nbsp; 4,'Share',</FONT> ><BR><FONT SIZE=3D2 FACE=3D"Courier = >nbsp; 5,'Shr Row Excl',</FONT> ><BR><FONT SIZE=3D2 FACE=3D"Courier = >nbsp; 6,'Excl',null) >lmode,</FONT> ><BR><FONT SIZE=3D2 FACE=3D"Courier = >New"> >decode(l.request,1,'No = >Lock',</FONT> ><BR><FONT SIZE=3D2 FACE=3D"Courier = >nbsp; 2,'Row Share',</FONT> ><BR><FONT SIZE=3D2 FACE=3D"Courier = >nbsp; 3,'Row Excl',</FONT> ><BR><FONT SIZE=3D2 FACE=3D"Courier = >nbsp; 4,'Share',</FONT> ><BR><FONT SIZE=3D2 FACE=3D"Courier = >nbsp; 5,'Shr Row Excl',</FONT> ><BR><FONT SIZE=3D2 FACE=3D"Courier = >nbsp; 6,'Excl',null) >request</FONT> ><BR><FONT SIZE=3D2 FACE=3D"Courier New">from >sys.gv_$lock l, = >sys.gv_$session s, sys.user$ u,sys.obj$ t</FONT> ><BR><FONT SIZE=3D2 FACE=3D"Courier New">where l.sid >=3D s.sid</FONT> ><BR><FONT SIZE=3D2 FACE=3D"Courier New">and s.type >!=3D = >'BACKGROUND'</FONT> ><BR><FONT SIZE=3D2 FACE=3D"Courier New">and t.obj# >=3D l.id1</FONT> ><BR><FONT SIZE=3D2 FACE=3D"Courier New">and u.user# >=3D t.owner#</FONT> ><BR><FONT SIZE=3D2 FACE=3D"Courier New">and >l.inst_id =3D = >s.inst_id</FONT> ><BR><FONT SIZE=3D2 FACE=3D"Courier New">/</FONT> ><BR><FONT SIZE=3D2 FACE=3D"Courier >New">prompt</FONT> ><BR><FONT SIZE=3D2 FACE=3D"Courier New">set >feedback on</FONT> ><BR><FONT SIZE=3D2 FACE=3D"Courier >New">prompt</FONT> ><BR><FONT SIZE=3D2 FACE=3D"Courier New">exit</FONT> > ><BR><FONT SIZE=3D2 FACE=3D"Courier = >=3D cut here = ></P> > ><P><FONT SIZE=3D2 FACE=3D"Courier New">Btu here is >the problem ... once = >in a while (aka many times a day) when we run this >script, we see = >objects as locked by some user which should NEVER >be even accessed. We = >have one schema that deals with out affiliates, and >it is practically = >independent of other schema in the database. Still >sometimes we see = >objects within the affiliate schema beign accesses >by other users who = >have nothing to do (or the code they execute has >nothing to do) with = >the objects displayed in the list.</FONT></P> > ><P><FONT SIZE=3D2 FACE=3D"Courier New">One peculier >thing I have noted, = >is affiliate schema used private synonyms and the >objects listed in the = >lock scripts are _always_ private synonyms pointing >to objects in the = >affiliate schema and the private synonym belongs to >the user who is = >_not_ the locking user.</FONT></P> > ><P><FONT SIZE=3D2 FACE=3D"Courier New">e.g.</FONT> ></P> > ><P><FONT SIZE=3D2 FACE=3D"Courier New">ABC1 = > >47,820 BROWNBRE >BRS02 = >TX-Trans Enq >SYSTEM_NETWORK_HIST = >MURPHYM Excl</FONT> ></P> > ><P><FONT SIZE=3D2 FACE=3D"Courier New">here locking >user ius brownbre = >locking system_network_hist owned by murphym. >Actually the table is = >owned by affiliate and murphym has a private >synonym to the = >table.</FONT></P> > ><P><FONT SIZE=3D2 FACE=3D"Courier New">Any clues? >DB is 9202 = >RAC.</FONT> ><BR><FONT SIZE=3D2 FACE=3D"Courier New">TIA</FONT> ><BR><FONT SIZE=3D2 FACE=3D"Courier New">Raj</FONT> ></P> > ><P><FONT SIZE=3D2 FACE=3D"Courier = >-------------</FONT> ><BR><FONT SIZE=3D2 FACE=3D"Courier New">Rajendra >dot Jamadagni at = >nospamespn dot com</FONT> -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
