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

创新互联-专业网站定制、快速模板网站建设、高性价比彭州网站开发、企业建站全套包干低至880元,成熟完善的模板库,直接使用。一站式彭州网站制作公司更省心,省钱,快速模板网站建设找我们，业务覆盖彭州地区。费用合理售后完善，10余年实体公司更值得信赖。

本文名称：Oracle查看锁和释放锁
网站路径：http://scyanting.com/article/jjocsh.html

Oracle查看锁和释放锁

其他资讯