Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

any script to find which user locking other user

Posted on 2014-03-24
16
Medium Priority
?
557 Views
Last Modified: 2014-03-25
Hey,

we are seeing locking sessions just want to know if the objects in same schema causing it or other schema is blocking this schema. how to find which session belong to which user is causing locks  which object in other user.
0
Comment
Question by:ajaybelde
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 4
16 Comments
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 39951020
0
 

Author Comment

by:ajaybelde
ID: 39951033
do you have script wich already written which will list the sid beoing locked and owner of the objects and locking user
0
 

Author Comment

by:ajaybelde
ID: 39951061
SELECT username U_NAME, owner OBJ_OWNER,
object_name, object_type, s.osuser,
DECODE(l.block,
  0, 'Not Blocking',
  1, 'Blocking',
  2, 'Global') STATUS,
  DECODE(v.locked_mode,
    0, 'None',
    1, 'Null',
    2, 'Row-S (SS)',
    3, 'Row-X (SX)',
    4, 'Share',
    5, 'S/Row-X (SSX)',
    6, 'Exclusive', TO_CHAR(lmode)
  ) MODE_HELD
FROM gv$locked_object v, dba_objects d,
gv$lock l, gv$session s
WHERE v.object_id = d.object_id
AND (v.object_id = l.id1)
AND v.session_id = s.sid
ORDER BY username, session_id;

does it look good
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 39951110
Try this....

It will show you locking chains (a blocking b blocking c blocking d, etc)  as well as identifying which row in a table (if a table lock) is in contention and sql being executed by each session currently (this is not necessarily the statement that created a lock though)

I use a scalar  subquery for the object lookup instead of a join, because it's possible that sessions are blocking each other but not because of object contention.  This could also be achieved with an outerjoin, mostly an aesthetic preference.


WITH lock_holders
     AS (SELECT *
           FROM gv$session
          WHERE COALESCE(blocking_instance, blocking_session) IS NOT NULL
             OR (inst_id, sid) IN (SELECT blocking_instance, blocking_session FROM gv$session))
    SELECT LPAD(' ', 3 * (LEVEL - 1)) || sid || DECODE(LEVEL, 1, ' root blocker') waiting_session,
           inst_id w_inst,
           serial#,
           status,
           event,
           username || ' - ' || osuser || ' - ' || machine user_info,
           (SELECT owner || '.' || object_name
              FROM dba_objects
             WHERE object_id = row_wait_obj#)
               waiting_on_object,
           CASE
               WHEN row_wait_obj# > 0
               THEN
                   DBMS_ROWID.rowid_create(
                       1,
                       row_wait_obj#,
                       row_wait_file#,
                       row_wait_block#,
                       row_wait_row#
                   )
           END
               waiting_on_rowid,
           (SELECT sql_text
              FROM v$sql s
             WHERE s.sql_id = h.sql_id)
               current_sql
      FROM lock_holders h
CONNECT BY PRIOR sid = blocking_session AND PRIOR inst_id = blocking_instance
START WITH blocking_session IS NULL;
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 39951140
Did you check out the script I posted?

Or look for another one here.
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 39951367
Sorry, thought it would be obvious to look in the "Database Performance" section.
Also I found an OLD script that was provided by Oracle (attached).
db-locks.sql
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39951535
wow, 1989, that is old!

My script is from the last millineum too but not quite that old and has been updated with new syntax and features several times since then.


based on the naming similarities I'm going to guess my original inspiration was the script you posted.  I could never remember why I called that WITH (which I originally wrote as an inline view) "lock_holders"  that always seemed to me like an inadequate name.
0
 
LVL 29

Expert Comment

by:MikeOM_DBA
ID: 39952090
Excuse me: utllockt.sql,v 1.2 1995/05/05 10:16:23
Modified for Oracle version 7!
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39952189
I was referring to the creation date.  utllockt has been updated a few more times since 95.  Just check $ORACLE_HOME/rdbms/admin in your most recent installation.

It's the same basic structure though,  that's why I wrote my own.  I wanted something I could run where I didn't have authority to create new tables.  Plus, it always seemed like a wasted step anyway.  And I needed a RAC aware, or actually an OPS-aware version, back then and utllockt doesn't cover that.
0
 

Author Comment

by:ajaybelde
ID: 39953955
can we identify based on history views like active session history
0
 

Author Comment

by:ajaybelde
ID: 39953969
and one more time is the script iam using cooorect.

SELECT username U_NAME, owner OBJ_OWNER,
object_name, object_type, s.osuser,
DECODE(l.block,
  0, 'Not Blocking',
  1, 'Blocking',
  2, 'Global') STATUS,
  DECODE(v.locked_mode,
    0, 'None',
    1, 'Null',
    2, 'Row-S (SS)',
    3, 'Row-X (SX)',
    4, 'Share',
    5, 'S/Row-X (SSX)',
    6, 'Exclusive', TO_CHAR(lmode)
  ) MODE_HELD
FROM gv$locked_object v, dba_objects d,
gv$lock l, gv$session s
WHERE v.object_id = d.object_id
AND (v.object_id = l.id1)
AND v.session_id = s.sid
ORDER BY username, session_id;

U_NAME  OBJ_OWNER  OBJECT_NAME      OBJECT_TYPE   STATUS MODE_HELD

SCHEMAB SchemaA    SCHEMA"A" object    TABLE  Row-X (SX)
schemab doesnot have any eread write privs on shema how come my above query shows schemb is locking schema A
0
 

Author Comment

by:ajaybelde
ID: 39953994
does AND v.session_id = s.sid  causing any confusion ?because in RAC  it may have same session sid on multiple nodes
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 2000 total points
ID: 39954025
>>> can we identify based on history views like active session history

not reliably

>>>  AND v.session_id = s.sid

if you are using the gv  views you need to also join on inst_id

try the query I posted above.  If you have table locks, it will tell you the exact rowid that is causing a lock
0
 

Author Comment

by:ajaybelde
ID: 39954764
sdstuber: does your script has to run all nodes?


and my out put is

 670
         1      19211 ACTIVE   enq: TM - contention                                             ORA_MON - oracle - server           ORA_MON.X                                                                                                                                                        AAAd0OAAAAAAAAAAAA
 lock table X in EXCLUSIVE mode
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 39955006
>>> sdstuber: does your script has to run all nodes?

no, you run it on one, but the gv$ views query the other nodes for you.  That's how it detects if session on nodeA is blocking a session on nodeB


that output doesn't look something any query in this thread would have produced.  That might be due to the formatting by putting it in the text box or maybe you missed some output in a cut-and-paste
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question