Oracle的锁表与解锁 |
02 | SELECT /*+ rule */ s.username, |
03 | decode(l.type, 'TM' , 'TABLE LOCK' , |
04 | 'TX' , 'ROW LOCK' , |
05 | NULL ) LOCK_LEVEL, |
06 | o.owner,o.object_name,o.object_type, |
07 | s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser |
08 | FROM v$session s,v$lock l,dba_objects o |
09 | WHERE l.sid = s.sid |
10 | AND l.id1 = o.object_id(+) |
11 | AND s.username is NOT Null |
12 |
13 | --kill session语句 |
14 | alter system kill session '50,492' ; |
15 | --以下几个为相关表 |
16 | SELECT * FROM v$lock; |
17 | SELECT * FROM v$sqlarea; |
18 | SELECT * FROM v$session; |
19 | SELECT * FROM v$process ; |
20 | SELECT * FROM v$locked_object; |
21 | SELECT * FROM all_objects; |
22 | SELECT * FROM v$session_wait; |
23 | --1.查出锁定object的session的信息以及被锁定的object名 |
24 | SELECT l.session_id sid, s.serial#, l.locked_mode,l.oracle_username, |
25 | l.os_user_name,s.machine, s.terminal, o.object_name, s.logon_time |
26 | FROM v$locked_object l, all_objects o, v$session s |
27 | WHERE l.object_id = o.object_id |
28 | AND l.session_id = s.sid |
29 | ORDER BY sid, s.serial# ; |
30 | --2.查出锁定表的session的sid, serial#,os_user_name, machine name, terminal和执行的语句 |
31 | --比上面那段多出sql_text和action |
32 | SELECT l.session_id sid, s.serial#, l.locked_mode, l.oracle_username, s. user #, |
33 | l.os_user_name,s.machine, s.terminal,a.sql_text, a. action |
34 | FROM v$sqlarea a,v$session s, v$locked_object l |
35 | WHERE l.session_id = s.sid |
36 | AND s.prev_sql_addr = a.address |
37 | ORDER BY sid, s.serial#; |
38 | --3.查出锁定表的sid, serial#,os_user_name, machine_name, terminal,锁的type,mode |
39 | SELECT s.sid, s.serial#, s.username, s.schemaname, s.osuser, s.process, s.machine, |
40 | s.terminal, s.logon_time, l.type |
41 | FROM v$session s, v$lock l |
42 | WHERE s.sid = l.sid |
43 | AND s.username IS NOT NULL |
44 | ORDER BY sid; |
45 |
46 | 这个语句将查找到数据库中所有的DML语句产生的锁,还可以发现, |
47 | 任何DML语句其实产生了两个锁,一个是表锁,一个是行锁。 |
48 | 杀锁命令 |
49 | alter system kill session 'sid,serial#' |
50 | SELECT /*+ rule */ s.username, |
51 | decode(l.type, 'TM' , 'TABLE LOCK' , |
52 | 'TX' , 'ROW LOCK' , |
53 | NULL ) LOCK_LEVEL, |
54 | o.owner,o.object_name,o.object_type, |
55 | s.sid,s.serial#,s.terminal,s.machine,s.program,s.osuser |
56 | FROM v$session s,v$lock l,dba_objects o |
57 | WHERE l.sid = s.sid |
58 | AND l.id1 = o.object_id(+) |
59 | AND s.username is NOT NULL |
60 | 如果发生了锁等待,我们可能更想知道是谁锁了表而引起谁的等待 |
61 | 以下的语句可以查询到谁锁了表,而谁在等待。 |
62 | 以上查询结果是一个树状结构,如果有子节点,则表示有等待发生。 |
63 | 如果想知道锁用了哪个回滚段,还可以关联到V$rollname,其中xidusn就是回滚段的USN |
64 | col user_name format a10 |
65 | col owner format a10 |
66 | col object_name format a10 |
67 | col object_type format a10 |
68 | SELECT /*+ rule */ lpad( ' ' ,decode(l.xidusn ,0,3,0))||l.oracle_username User_name, |
69 | o.owner,o.object_name,o.object_type,s.sid,s.serial# |
70 | FROM v$locked_object l,dba_objects o,v$session s |
71 | WHERE l.object_id=o.object_id |
72 | AND l.session_id=s.sid |
73 | ORDER BY o.object_id,xidusn DESC |
lock table 表名 exclusive mode nowait; -- 锁整个表 select * from 表名 where XXX for update nowaitl -- 锁符合条件的记录