1、查询数据库阻塞情况：
select event,count(*) from gv$session_wait where event not like '%idle%' group by event order by count(0) desc;
如果上面结果的Event有Library或Lock或pin S wait字样，说明数据库有锁或阻塞，继续执行下面sql进一步定位：
select 'alter system kill session '''|| s.sid ||',' || s.serial# || ',@'|| s.inst_id ||''' immediate;', s.*
from gv$session s where s.event like 'XXXXXXX%' --xxxx用上面sql结果的Event内容代替（如library）确定有问题的会话，可以用上面自动生成的kill命令终止，但需谨慎执行。
如果上面查询没有发现问题，可以用下面sql检查会话相互锁定的情况
select 'alter system kill session '''|| sid ||',' || serial# || ',@'|| inst_id ||''' immediate;'
from gv$session where sid in (
select blocking_session from gv$session where blocking_session is not null);如果有查询结果，等待2-3秒后再次查询，如果还有有同样的结果，可用上面生成的kill语句终止会话。
2、查询最终阻塞者进程：
select 'alter system kill session '''|| sid ||',' || serial# || ',@'|| inst_id ||''' immediate;', sys_connect_by_path(a.SID || '@' || a.inst_id, ' <- ') tree, -- tree最后一个为最终阻塞者. a.inst_id, a.process, a.sid, a.serial#, a.sql_id, a.event, a.status, a.program, a.machine, connect_by_isleaf as isleaf, level as tree_level from gv$session a start with a.blocking_session is not null connect by (a.sid || '@' || a.inst_id) = prior (a.blocking_session || '@' || a.blocking_instance);
3、ORA-14450: 试图访问已经在使用的事务处理临时表：
-- ORA-14450: 试图访问已经在使用的事务处理临时表 select 'alter system kill session ''' || a.SID || ',' || a.SERIAL# || ',@' || inst_id || ''' immediate;' ,a.* from gV$session a where a.SID in (select sid from gv$enqueue_lock t where t.type='TO') --and status='INACTIVE' --and a.program like '%JDBC Thin Client%' ;
4、SQL Server查询锁的辅助存储过程（master库创建下面2个存储过程）
ALTER PROCEDURE [dbo].[sp_who3] ( @SessionID int = NULL )
AS
BEGIN
SELECT
SPID = er.session_id
,Status = ses.status
,[Login] = ses.login_name
,Host = ses.host_name
,BlkBy = er.blocking_session_id
,DBName = DB_Name(er.database_id)
,CommandType = er.command
,SQLStatement = st.text
,ObjectName = OBJECT_NAME(st.objectid)
,ElapsedMS = er.total_elapsed_time
,CPUTime = er.cpu_time
,IOReads = er.logical_reads + er.reads
,IOWrites = er.writes
,LastWaitType = er.last_wait_type
,StartTime = er.start_time
,Protocol = con.net_transport
,ConnectionWrites = con.num_writes
,ConnectionReads = con.num_reads
,ClientAddress = con.client_net_address
,Authentication = con.auth_scheme
FROM sys.dm_exec_requests er
OUTER APPLY sys.dm_exec_sql_text(er.sql_handle) st
LEFT JOIN sys.dm_exec_sessions ses
ON ses.session_id = er.session_id
LEFT JOIN sys.dm_exec_connections con
ON con.session_id = ses.session_id
WHERE er.session_id > 50
AND @SessionID IS NULL OR er.session_id = @SessionID
ORDER BY
er.blocking_session_id DESC ,er.session_id
END;ALTER PROCEDURE [dbo].[sp_who_lock]
as
begin
declare @spid int,@bl int,
@intTransactionCountOnEntry int,
@intRowcount int,
@intCountProperties int,
@intCounter int
create table #tmp_lock_who (
id int identity(1,1),
spid smallint,
bl smallint)
IF @@ERROR<>0 RETURN @@ERROR
insert into #tmp_lock_who(spid,bl) select 0 ,blocked
from (select * from sysprocesses where blocked>0 ) a
where not exists(select * from (select * from sysprocesses where blocked>0 ) b
where a.blocked=spid)
union select spid,blocked from sysprocesses where blocked>0
IF @@ERROR<>0 RETURN @@ERROR
-- 找到临时表的记录数
select @intCountProperties = Count(*),@intCounter = 1
from #tmp_lock_who
IF @@ERROR<>0 RETURN @@ERROR
if @intCountProperties=0
select '现在没有阻塞和死锁信息' as message
-- 循环开始
while @intCounter <= @intCountProperties
begin
-- 取第一条记录
select @spid = spid,@bl = bl
from #tmp_lock_who where Id = @intCounter
begin
if @spid =0
select '引起数据库死锁的是: '+ CAST(@bl AS VARCHAR(10)) + '进程号,其执行的SQL语法如下'
else
select '进程号SPID：'+ CAST(@spid AS VARCHAR(10))+ '被' + '进程号SPID：'+ CAST(@bl AS VARCHAR(10)) +'阻塞,其当前进程执行的SQL语法如下'
DBCC INPUTBUFFER (@bl )
end
-- 循环指针下移
set @intCounter = @intCounter + 1
end
drop table #tmp_lock_who
return 0
end;在master等数据库执行存储过程sp_who3,会列出目前正在执行的sql，其中：
Host列表示执行sql的电脑；
ElapsedMS表示当前sql已执行的时间，单位为毫秒；
BlkBy表示该会话被其他SPID阻塞，可按SPID列找到导致阻塞的会话，注意阻塞链条可能有多个
