Oracle 锁和闩 锁表查询

--锁表查询SQL
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;

--释放SESSION SQL:
--alter system kill session 'sid, serial#';
ALTER system kill session '1065, 57065';



-- DML锁
-- 事务锁 `TX锁`
-- 事务,锁,会话的系统视图
SELECT * FROM V$transaction ; 
SELECT * FROM V$SESSION ;
-- 队列表，锁表
SELECT * FROM v$lock;
-- scoot测试表模拟排他锁事务
CREATE TABLE dept AS SELECT * FROM scott.dept;
SELECT * FROM dept;
CREATE TABLE emp AS SELECT * FROM scott.emp;
SELECT * FROM emp;
ALTER TABLE dept ADD CONSTRAINT dept_pk PRIMARY KEY(deptno);
ALTER TABLE emp ADD CONSTRAINT emp_pk PRIMARY KEY(empno);
CREATE INDEX emp_dept_idex ON emp(deptno);
-- 模拟
-- 会话1
UPDATE dept SET dname = INITCAP(dname);
-- 会话2
UPDATE dept SET dname = INITCAP(dname);
-- 会话3
UPDATE emp SET ename= UPPER(ename);
-- lmode=6 :排他锁
-- request=0:没有发出请求,你拥有这个锁,如果不为0,则是一个等待的排队请求
SELECT username,v$lock.sid,TRUNC(id1/POWER(2,16)) rbs,
BITAND(id1,to_number('ffff','xxxx'))+0 slot,
id2 seq,
lmode,
request
FROM v$lock,v$session 
WHERE v$lock.type = 'TX'
AND v$lock.sid = v$session.sid
AND v$session.USERNAME = USER;   
-- 事务id(usn,slot,sqn) 对应于上面sql的(rbs,slot,seq)
SELECT xidusn,xidslot,xidsqn FROM v$transaction;
-- v$lock自连接找出阻塞的sid
SELECT 
   (SELECT username FROM v$session WHERE SID=a.sid) blocker,
   a.sid,
   'is_blocking',
   (SELECT username FROM v$session WHERE SID=b.sid) blockee,
   b.sid
FROM v$lock a , v$lock b
WHERE a.BLOCK = 1 
AND b.REQUEST = 0
AND a.id1 = b.id1
AND a.id2 = b.id2;