oracle 查看锁表情况并处理锁表

[sql]  view plain  copy

 print ?

1. /*  
2. *locked  
3. *query locked object and analyse reason,kill it  
4. *  
5. */  
6.   
7. select 'alter system kill session ''' || SID || ',' || SERIAL# || ''';'  
8.   from (select distinct a.sid,  
9.                         a.Serial#,  
10.                         status,  
11.                         machine,  
12.                         LOCKWAIT,  
13.                         logon_time  
14.           from v$session a, v$locked_object b  
15.          where (a.status = 'ACTIVE' or a.status = 'INACTIVE')  
16.            and a.sid = b.session_id  
17.            and b.ORACLE_USERNAME = 'XYHISTEST' --加上用户名可过滤用户资源  
18.         )  
19.          
20. /*2、批量执行第一步生成的语句  
21. alter system kill session 'sid,serial#';  
22. alter system kill session '6976,33967';*/     
23.   
24. /*3、查询oracle用户名，机器名，锁表对象*/  
25. SELECT l.session_id sid,  
26.        s.serial#,  
27.        l.locked_mode,  
28.        l.oracle_username,  
29.        l.os_user_name,  
30.        s.machine,  
31.        s.terminal,  
32.        o.object_name,  
33.        s.logon_time  
34.   FROM v$locked_object l, all_objects o, v$session s  
35.  WHERE l.object_id = o.object_id  
36.    AND l.session_id = s.sid  
37.  ORDER BY sid, s.serial#;  
38.   
39. /*3.或者*/  
40. select s.SID, s.OSUSER, p.spid as OSPID, s.MACHINE, s.TERMINAL, s.PROGRAM  
41.   from v$session s, v$process p  
42.  where s.sid = 6 --session_id  
43.    and s.paddr = p.addr;  
44.   
45. /*4、查询是执行何sql语句导致锁表的*/  
46. select b.sql_text  
47.   from v$session a, v$sql b  
48.  where a.sid = 6 --session_id  
49.    and a.SQL_ADDRESS = b.ADDRESS(+);  
50.   
51. /*5、查询是执行何sql语句导致锁表的*/  
52. SELECT l.session_id sid,  
53.        s.serial#,  
54.        l.locked_mode,  
55.        l.oracle_username,  
56.        s.user#,  
57.        l.os_user_name,  
58.        s.machine,  
59.        s.terminal,  
60.        a.sql_text,  
61.        a.action  
62.   FROM v$sqlarea a, v$session s, v$locked_object l  
63.  WHERE l.session_id = s.sid  
64.    AND s.prev_sql_addr = a.address  
65.  ORDER BY sid, s.serial#;  

--以下几个为相关表
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;


--查看被锁的表 
select b.owner,b.object_name,a.session_id,a.locked_mode from v$locked_object a,dba_objects b where b.object_id = a.object_id;


--查看那个用户那个进程照成死锁
select b.username,b.sid,b.serial#,logon_time from v$locked_object a,v$session b where a.session_id = b.sid order by b.logon_time;


--查看连接的进程 
SELECT sid, serial#, username, osuser FROM v$session;


--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语句其实产生了两个锁，一个是表锁，一个是行锁。


--杀掉进程 sid,serial#
alter system kill session'210,11562';