SQL:关于处理数据库的表锁
在处理Oracle的锁表的时候,以下的SQL会Helpful:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
set linesize 300 col host_name for a30 col owner for a20 col object_name for a30 select rpad(oracle_username,10) owner,session_id sid, decode(locked_mode,0,'None',1,'Null',2,'Row share',3,'Row exclusive',4,'Share',5,'Share Row Exclusive',6,'Exclusive') lock_type, object_name,xidusn,xidslot,xidsqn from v$locked_object,all_objects where v$locked_object.object_id=all_objects.object_id; select inst_id,sid,type,id1,id2,decode(lmode,0,'None',1,'Null',2,'Row share',3,'Row Exclusive',4,'Share',5,'Share Row Exclusive', 6,'Exclusive') lock_type,request,ctime,block from gv$lock where type in ('TX','TM'); (对于RAC环境,可能有的锁不在当前节点,"gv$"是查全集群状态的性能表) select s.username,l.OBJECT_ID,l.session_id,s.serial#,l.oracle_username,l.os_user_name,l.process from v$locked_object l,v$session s where l.session_id=s.sid; alter system kill session 'session id,serial'; select instance_number,instance_name,host_name from v$instance; 2015年12月4日18:19:41 |
————————
Done。