--查找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余年实体公司更值得信赖。