运维过程中经常碰到数据库出现大量的锁的情况,以下记录了数据库锁处理的详细过程:
检查当前的数据库锁情况,获取对应的SID:
set line 150 pagesize 30000
select /*+ rule */ INST_ID, SID, TYPE, ID1, ID2, LMODE, REQUEST, CTIME, BLOCK
from gv$lock where (ID1,ID2,TYPE) in
(select ID1,ID2,TYPE from gv$lock where request>0)
order by ctime desc;
根据SID查询会话的详情,可能是某个用户在手动操作数据导致锁,需要和应用侧进一步确认锁的情况:
col sql_id for a15
col status for a10
col machine for a15
col event for a15
select username,machine,schemaname,sql_id,event,status,program from v$session where sid in (9926);
确认完毕以后,如果可以杀掉对应的会话,此时根据SID查询操作系统进程:
select p.spid from v$session s,v$process p where s.paddr=p.addr and s.sid=6562;
根据SID生成操作系统的kill命令:
select /*+ rule */
'ps -ef|grep LOCAL=NO |grep '||p.spid||'| awk ''{print $2}'' |xargs kill -9 '
from v$session s, v$process p
where s.paddr = p.addr
and s.sid in (2247,4775,4170,1132,5224);