oracle手动锁表和解锁_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          1SYS >alter system kill session '243,15622,@1' immediate;再查询gv$sessionSYS >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 contactProcess ID: 10189Session ID: 243 Serial number: 15622Whether or not the session has an ongoing transaction, Oracle Database does notrecover the entire session state until the session user issues a request to the session andreceives a message that the session has been terminated.IMMEDIATE Specify IMMEDIATE to instruct Oracle Database to roll back ongoingtransactions, release all session locks, recover the entire session state, and returncontrol to you immediately.根据oracle的文档，在没有immediate字句情况下，oracle终结会话后，直到会话用户发起请求并提示会话终结后，才会恢复该会话状态。而immediate子句的作用是终结会话后，马上恢复该会话状态，所以前面例子操作后查询gv$session，已经没有该会话状态。