ajaybelde
asked on
any script to find which user locking other user
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.
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.
Lock's &Latches
ASKER
do you have script wich already written which will list the sid beoing locked and owner of the objects and locking user
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
Also I found an OLD script that was provided by Oracle (attached).
db-locks.sql
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.
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.
Excuse me: utllockt.sql,v 1.2 1995/05/05 10:16:23
Modified for Oracle version 7!
Modified for Oracle version 7!
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.
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.
ASKER
can we identify based on history views like active session history
ASKER
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
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
ASKER
does AND v.session_id = s.sid causing any confusion ?because in RAC it may have same session sid on multiple nodes
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
>>> 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
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