ORACLE锁表相关语句

简单记录，只贴SQL

--死锁级别：
--0：none
--1：null 空
--2：Row-S 行共享(RS)：共享表锁
--3：Row-X 行专用(RX)：用于行的修改
--4：Share 共享锁(S)：阻止其他DML操作
--5：S/Row-X 共享行专用(SRX)：阻止其他事务操作
--6：exclusive 专用(X)：独立访问使用

--查询锁表
SELECT
	s.sid,
	s.serial#,
	l.oracle_username AS 登陆账号名称,
	l.os_user_name AS 登录电脑名称,
	a.object_name AS 被锁表名,
	l.locked_mode AS 死锁级别
FROM
	v$locked_object l,
	dba_objects a,
	v$session s 
WHERE
	a.object_id = l.object_id AND l.session_id = s.sid;

--解锁语句
alter system kill session '查询到的SID,查询到的SERIAL#';

--查询导致锁表的SQL
SELECT
	N.USERNAME,
	N.MACHINE,
	N.PROGRAM,
	N.SID,
	N.SERIAL#,
	N.STATUS,
	T.PIECE,
	T.SQL_TEXT 
FROM
	V$SESSION N,
	V$SQLTEXT T 
WHERE
	N.SID IN ( SELECT DISTINCT S.SID FROM V$LOCKED_OBJECT L, V$SESSION S WHERE L.SESSION_ID = S.SID ) 
	AND N.SQL_ADDRESS = T.ADDRESS ( + ) 
ORDER BY
	T.PIECE;