于ORACLE的表与解

- 日理万妓 2022-07-16 14:21 115阅读 0赞

总结1：**Oracle**的锁表与解锁   
select  
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;

\--kill session语句  
alter system kill session'50,492';  
\--以下几个为相关表  
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\#, 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 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

总结2：一个有用查找脚本：

column sid format 999;  
column b format 9;  
column object\_name format a30;  
column locktype format a20;  
select v$lock.sid,  
decode(v$lock.type,  
        'MR', 'Media Recovery',  
        'RT','Redo Thread',  
        'UN','User Name',  
        'TX', 'Transaction',  
        'TM', 'DML',  
        'UL', 'PL/SQL User Lock',  
        'DX', 'Distributed Xaction',  
        'CF', 'Control File',  
        'IS', 'Instance State',  
        'FS', 'File Set',  
        'IR', 'Instance Recovery',  
        'ST', 'Disk Space Transaction',  
        'TS', 'Temp Segment',  
        'IV', 'Library Cache Invalida-tion',  
        'LS', 'Log Start or Switch',  
        'RW', 'Row Wait',  
        'SQ', 'Sequence Number',  
        'TE', 'Extend Table',  
        'TT', 'Temp Table',  
        'Unknown') LockType,  
         rtrim(owner) || '.' || object\_name object\_name,  
decode(lmode,   0, 'None',  
                1, 'Null',  
                2, 'Row-S',  
                3, 'Row-X',  
                4, 'Share',  
                5, 'S/Row-X',  
                6, 'Exclusive','Unknown') LockMode,  
decode(request, 0, 'None',  
                1, 'Null',  
                2, 'Row-S',  
                3, 'Row-X',  
                4, 'Share',  
                5, 'S/Row-X',  
                6, 'Exclusive', 'Unknown') RequestMode,  
ctime, block b  
from v$lock, all\_objects  
where sid > 6  
and v$lock.id1 = all\_objects.object\_id;

找到某表的锁 所属的sid.  
alter system kill session 'sid,serial\#';  
即可

select object\_id,session\_id,serial\#,oracle\_username,os\_user\_name,s.process  
from v$locked\_object a,v$session s  
where a.session\_id=s.sid;  
查出被lock 的对象  
然后 alter system kill session 'sid,serial\#';

知识点3：

LOCK   TABLE    
  语法：    
  LOCK   TABLE   table\_1   \[,table\_2,   ...,   table\_n\]   IN   lock\_mode   MODE    
  NOWAIT    
  变量：    
  table\_1,...,table\_n:   一系列你想通过使用LOCK   TABLE语句锁住的数据库表。    
  lock\_mode:   对于某一数据库表你要设定的锁定模式。你可以从如下的锁定模式中任选一个。    
  &\#61510;&\#61472;EXCLUSIVE    
  &\#61510;&\#61472;SHARE   ROW   EXCLUSIVE    
  &\#61510;&\#61472;SHARE    
  &\#61510;&\#61472;SHARE   UPDATE    
  &\#61510;&\#61472;ROW   SHARE    
  &\#61510;&\#61472;ROW   EXCLUSIVE    
  NOWAIT:   Oracle   will   not   wait   to   lock   the   given   Table(s),   if   the   Table(s)   is(are)   not    
  available    
  例子：    
  SQL    
  LOCK   TABLE   loan   IN   SHARE   MODE   ;    
  LOCK   TABLE   region   IN   EXCLUSIVE   MODE   NOWAIT;    
  LOCK   TABLE   acct   IN   SHARE   UPDATE   MODE;    
  LOCK   TABLE   bank   IN   ROW   EXCLUSIVE   MODE   NOWAIT;    
  LOCK   TABLE   user   IN   SHARE   ROW   EXCLUSIVE   MODE;    
  LOCK   TABLE   branch   IN   ROW   SHARE   MODE   NOWAIT;    
     
  commit    
  /