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