Oracle 查看锁和释放锁

--查找sid和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
-----------
125 199 SYS
oracle
TESTLOCK
3


SID SERIAL# ORACLE_USERNAME
---------- ---------- ------------------------------
OS_USER_NAME
------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------
LOCKED_MODE
-----------
50 181 SYS
oracle
TESTLOCK
3



--释放SESSION SQL:
alter system kill session '50,128';



--查找锁的SQLid
SQL> select sid,event,sql_id from v$session where sid in ('125','50');

SID EVENT
---------- ----------------------------------------------------------------
SQL_ID
-------------
50 enq: TX - row lock contention
3c8u4r1z8u9ss

125 SQL*Net message from client
fvk2xw29t6hw4



--查看SQL的执行计划
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
--------------------------------------------------------------------------------


23 rows selected.


--查找SQL的语句
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
---------- -- ---------- ---------- ---------- ---------- ---------- ----------
125 TX 655382 969 6 0 9699 1
50 TX 655382 969 0 6 9622 0