oracle的锁表和解锁

 SELECT /*+ rule */ s.username,
 decode(l.type,'TM','TABLE LOCK',
 'TX','ROW LOCK',
 NULL) LOCK_LEVEL,
 o.owner,o.object_name,o.object_type,
 s.sid as sid,s.serial# as serial#,s.terminal,s.machine,s.program,s.osuser
 FROM v$session s,v$lock l,dba_objects o
 WHERE l.sid = s.sid
 AND l.id1 = o.object_id(+)
 AND s.username is NOT NULL
 
--kill session语句alter system kill session'sid,serial#' immediate;
 alter system kill session'10,46004' immediate;
 --以下几个为相关表
 SELECT * FROM v$lock;
 SELECT * FROM v$sqlarea;
 SELECT * FROM v$session;
 SELECT * FROM v$process ;
 SELECT * FROM v$locked_object;
 SELECT * FROM all_objects;
 SELECT * FROM v$session_wait;
--1.查出锁定object的session的信息以及被锁定的object名
SELECT l.session_id sid, s.serial# as serial#, l.locked_mode,l.oracle_username,
 l.os_user_name,s.machine, s.terminal, o.object_name, s.logon_time
 FROM v$locked_object l, all_objects o, v$session s
 WHERE l.object_id = o.object_id
 AND l.session_id = s.sid
 ORDER BY sid, s.serial# ;
 --2.查出锁定表的session的sid, serial#,os_user_name, machine name, terminal和执行的语句
 --比上面那段多出sql_text和action
 SELECT l.session_id sid, s.serial#, l.locked_mode, l.oracle_username, s.user#,
 l.os_user_name,s.machine, s.terminal,a.sql_text, a.action
 FROM v$sqlarea a,v$session s, v$locked_object l
 WHERE l.session_id = s.sid
 AND s.prev_sql_addr = a.address
 ORDER BY sid, s.serial#;
 --3.查出锁定表的sid, serial#,os_user_name, machine_name, terminal，锁的type,mode
 SELECT s.sid, s.serial#, s.username, s.schemaname, s.osuser, s.process, s.machine,
 s.terminal, s.logon_time, l.type
 FROM v$session s, v$lock l
 WHERE s.sid = l.sid
 AND s.username IS NOT NULL
 ORDER BY sid;
 
--这个语句将查找到数据库中所有的DML语句产生的锁，还可以发现，
 --任何DML语句其实产生了两个锁，一个是表锁，一个是行锁。
 --杀锁命令
 alter system kill session 'sid,serial#'
 SELECT /*+ rule */ s.username,
 decode(l.type,'TM','TABLE LOCK',
 'TX','ROW LOCK',
 NULL) LOCK_LEVEL,
 o.owner,o.object_name,o.object_type,
 s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser
 FROM v$session s,v$lock l,dba_objects o
 WHERE l.sid = s.sid
 AND l.id1 = o.object_id(+)
 AND s.username is NOT NULL
 --以上查询结果是一个树状结构，如果有子节点，则表示有等待发生。
 --如果想知道锁用了哪个回滚段，还可以关联到V$rollname，其中xidusn就是回滚段的USN
 col user_name format a10
 col owner format a10
 col object_name format a10
 col object_type format a10
  --如果发生了锁等待，我们可能更想知道是谁锁了表而引起谁的等待
 --以下的语句可以查询到谁锁了表，而谁在等待。
 SELECT /*+ rule */ lpad(' ',decode(l.xidusn ,0,3,0))||l.oracle_username User_name,
 o.owner,o.object_name,o.object_type,s.sid,s.serial#
 FROM v$locked_object l,dba_objects o,v$session s
 WHERE l.object_id=o.object_id
 AND l.session_id=s.sid
 ORDER BY o.object_id,xidusn DESC

 ===================================

SYS >select sid,serial#,status,server,inst_id from gv$session where username='APP';

       SID    SERIAL# STATUS   SERVER       INST_ID
---------- ---------- -------- --------- ----------
        20       1925 INACTIVE DEDICATED          1



不加immedate，kill session后查询gv$session，该会话记录STATUS是KILLED。

SYS >alter system kill session '20,1925,@1';
SYS >select sid,serial#,status,server,inst_id from gv$session where username='APP';

       SID    SERIAL# STATUS   SERVER       INST_ID
---------- ---------- -------- --------- ----------
        20       1925 KILLED   PSEUDO             1


在用户app的会话中操作，提示如下：
APP >select * from tab;
select * from tab
*
ERROR at line 1:
ORA-00028: your session has been killed


再查询gv$session，该会话记录已经没有了。
SYS >select sid,serial#,status,server,inst_id from gv$session where username='APP';

no rows selected




用app用户重新登录，再加上immediate子句操作：
SYS >select sid,serial#,status,server,inst_id from gv$session where username='APP';

       SID    SERIAL# STATUS   SERVER       INST_ID
---------- ---------- -------- --------- ----------
       243      15622 INACTIVE DEDICATED          1


SYS >alter system kill session '243,15622,@1' immediate;

再查询gv$session
SYS >select sid,serial#,status,server,inst_id from gv$session where username='APP';

no rows selected

这是可以看到gv$session已经没有记录了。


而app用户查询，直接提示连接断开了。
APP >select * from tab;
select * from tab
*
ERROR at line 1:
ORA-03135: connection lost contact
Process ID: 10189
Session ID: 243 Serial number: 15622



Whether or not the session has an ongoing transaction, Oracle Database does not
recover the entire session state until the session user issues a request to the session and
receives a message that the session has been terminated.

IMMEDIATE Specify IMMEDIATE to instruct Oracle Database to roll back ongoing
transactions, release all session locks, recover the entire session state, and return
control to you immediately.

根据oracle的文档，在没有immediate字句情况下，oracle终结会话后，直到会话用户发起请求并提示会话终结后，才会恢复该会话状态。而immediate子句的作用是终结会话后，马上恢复该会话状态，所以前面例子操作后查询gv$session，已经没有该会话状态。