Oracle view Lock and Release lock

--Find SIDs and serial#
SELECT object_name, Machine, S.sid, s.serial#
From Gv$locked_object L, dba_objects O, gv$session s
WHERE l.object_id = o.object_id
and l.session_id = S.sid;


SID serial# Oracle_username
---------- ---------- ------------------------------
Os_user_name
------------------------------
object_name
--------------------------------------------------------------------------------
Locked_mode
-----------
199 SYS
Oracle
Testlock
3


SID serial# Oracle_username
---------- ---------- ------------------------------
Os_user_name
------------------------------
object_name
--------------------------------------------------------------------------------
Locked_mode
-----------
181 SYS
Oracle
Testlock
3



--Release session SQL:
Alter system kill session ' 50,128 ';



--Find the sqlid of the lock
Sql> Select sid,event,sql_id from v$session where SID in (' 125 ', ' 50 ');

SID EVENT
---------- ----------------------------------------------------------------
sql_id
-------------
Enq:tx-row Lock contention
3c8u4r1z8u9ss

Sql*net Message from Client
Fvk2xw29t6hw4



--View the SQL execution plan
Sql> select * FROM table (dbms_xplan.display_cursor (' 3c8u4r1z8u9ss '));

Plan_table_output
--------------------------------------------------------------------------------
sql_id 3C8U4R1Z8U9SS, child number 0
-------------------------------------
Update Testlock set num=8 where num=7

Plan Hash value:2631153153

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
-------------------------------------------------------------------------------
| 0 |          UPDATE STATEMENT |       |       |     |          2 (100) | |
|  1 | UPDATE |       Testlock |       |            |          | |

Plan_table_output
--------------------------------------------------------------------------------
|* 2 | TABLE ACCESS full|     Testlock |    1 |     13 | 2 (0) | 00:00:01 |
-------------------------------------------------------------------------------

predicate information (identified by Operation ID):
---------------------------------------------------

2-filter ("NUM" =7)

Note
-----
-Dynamic Statistics used:dynamic sampling (level=2)

Plan_table_output
--------------------------------------------------------------------------------


Rows selected.


--Find the SQL statement
Select Sql_text from V$sqltext where sql_id= ' 3c8u4r1z8u9ss ';



--
Sql> Select Sid,type,id1,id2,lmode,request,ctime,block from V$lock where block=1 or request<>0;

SID TY ID1 ID2 lmode REQUEST CTIME BLOCK
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
TX 655382 969 6 0 9699 1
TX 655382 969 0 6 9622 0

This article is from the "SYSDBA" blog, make sure to keep this source http://sysdba.blog.51cto.com/10492366/1704545

Oracle view Lock and Release lock