Oracle-L: RE: Question with lock script

From: Stephane Faroult <sfaroult_at_oriolecorp.com>
Date: Mon, 06 Oct 2003 08:14:24 -0800
Message-ID: <F001.005D22C5.20031006081424@fatcity.com>

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"
><Rajendra.Jamadagni_at_espn.com>
>To: Multiple recipients of list ORACLE-L
><ORACLE-L_at_fatcity.com>
>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>
>
>Hi
>all,
>
>
>I am using
>following script to =
>see locks in the DB. Do you see any problems with
>it? 
>
>
>=3D 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
> New">select --+ no_merge(l) =
>no_merge(s)
> New">
>(select instance_name =
>
> sys.gv_$instance 
> instance_number =3D l.inst_id) inst_id,
> New">
>l.sid || ',' || =
>s.serial# sid,
> New">
>s.username,
> New"> =
>replace(s.terminal,'WTS-') terminal,
> New">
>decode(l.type,
> nbsp; 'RW','RW-Row Wait Enq',
> nbsp; 'TM','TM-DML Enq',
> nbsp; 'TX','TX-Trans Enq',
> nbsp; 'UL','UL-User',l.type||'-System')
>res,
> New">
>t.name =
>table_name,u.name owner,
> New">
>l.id1,l.id2,
> New">
>decode(l.lmode,1,'No =
>Lock',
> nbsp; 2,'Row Share',
> nbsp; 3,'Row Excl',
> nbsp; 4,'Share',
> nbsp; 5,'Shr Row Excl',
> nbsp; 6,'Excl',null)
>lmode,
> New">
>decode(l.request,1,'No =
>Lock',
> nbsp; 2,'Row Share',
> nbsp; 3,'Row Excl',
> nbsp; 4,'Share',
> nbsp; 5,'Shr Row Excl',
> nbsp; 6,'Excl',null)
>request
> from
>sys.gv_$lock l, =
>sys.gv_$session s, sys.user$ u,sys.obj$ t
> where l.sid
>=3D s.sid
> and s.type
>!=3D =
>'BACKGROUND'
> and t.obj#
>=3D l.id1
> and u.user#
>=3D t.owner#
> and
>l.inst_id =3D =
>s.inst_id
> /
> New">prompt
> set
>feedback on
> New">prompt
> exit
>
> =3D 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

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriolecorp.com

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: ListGuru_at_fatcity.com (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).

Received on Mon Oct 06 2003 - 11:14:24 CDT

RE: Question with lock script - phantom objects