oracle通过什么命令释放锁,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