【检查ORACLE阻塞】如果阻塞超过N秒则发短信报警并KILL进程

检查oracle阻塞，如果阻塞超过N秒则发短信报警
阻塞超过分钟后自动kill进程
检查oracle分布式事务预留锁，回滚事务

/*** 注意：链接服务器clinicdb要设置RPC和RPC Out 为true，否则无法自动解锁***/
CREATE PROCEDURE [dbo].[pCheckOracleLock]
AS
SET  NOCOUNT ON
DECLARE @phones varchar(500)
DECLARE @i int
DECLARE @sid  int
DECLARE @serial  int
DECLARE @ctime  int

DECLARE @txt varchar(4000)
--Declare @killTxt varchar(100)

SELECT IDENTITY(int ,1,1) ID,* INTO #tmp FROM OPENQUERY(clinicdb,'select * from apps.Locks where ctime>100')

IF EXISTS(SELECT * FROM #tmp)
BEGIN	
	SELECT @phones=replace(value,' ','') FROM OPENQUERY(clinicdb,'select value from core.parameterValue where name =''DutyPhoneNums'' ')
	SET @phones=replace(@phones,'，',',')

	IF @phones IS NULL OR @phones='' 
		RAISERROR('pCheckOracleLock存储过程执行错误：系统参数DutyPhoneNums未设置',18,1)
		
	SET @i=1
	SET @txt=''
	WHILE @i<=(SELECT max(ID) FROM #tmp)
	BEGIN		
		SELECT @txt='Oracle出现阻塞：SID='+ltrim(SID)+'，MACHINE='+isnull(MACHINE,'')+'，PROGRAM='+isnull(PROGRAM,'')+'，obj='+lower(isnull(OWNER,'')+'.'+isnull(OBJECT_NAME,''))
			+'，已阻塞'+isnull(ltrim(ctime),'')+'秒'
			+CASE WHEN ctime IS NOT NULL AND ctime>600 THEN '，【将立即终止该进程！！！】' ELSE '' END
			+'，SQL:'+char(13)+char(10)+LEFT(isnull(SQL_TEXT,''),150)
		FROM #tmp WHERE ID=@i
		
		INSERT INTO [sy30smoke].[JX_T20_CRM].DBO.tSMSSend(content,mobiletelePhone,typeflag,statusflag,appenddatetime)
			SELECT @txt, t.s, 16 AS typeflag, 0 AS statusflag, getdate() AS appenddatetime
			FROM dbo.[fnSplitString](@phones,',') t
		 
		SET @i=@i+1
	END
	
	SET @i=1
	WHILE @i<=(SELECT max(ID) FROM #tmp)
	BEGIN
		SELECT @ctime=ctime, @serial=serial, @sid=[sid] 
		FROM #tmp WHERE ID=@i
				 
		IF @ctime>600
		BEGIN
			EXEC('begin
			pKillSession(?,?);
			commit;
			end;', @sid,@serial ) at clinicdb
		END		 
		 
		SET @i=@i+1
	END
END

--分布式事务预留锁
SELECT IDENTITY(int ,1,1) ID,* INTO #pending
FROM OPENQUERY(clinicdb,'select LOCAL_TRAN_ID, to_char(Fail_Time,''yyyy-mm-dd HH24:mi:ss'') as Fail_Time, Host from dba_2pc_pending where fail_time>sysdate-1 and fail_time is not null and state=''prepared'' ')

IF EXISTS(SELECT * FROM #pending)
BEGIN
	SELECT @phones=replace(value,' ','') FROM OPENQUERY(clinicdb,'select value from core.parameterValue where name =''DutyPhoneNums'' ')
	SET @phones=replace(@phones,'，',',')

	IF @phones IS NULL OR @phones='' 
		RAISERROR('pCheckOracleLock存储过程执行错误：系统参数DutyPhoneNums未设置',18,1)
	
	DECLARE @dt datetime
	SET @dt=dateadd(second,-600,getdate()) 
	
	SET @i=1
	SET @txt=''
	WHILE @i<=(SELECT max(ID) FROM #pending)
	BEGIN
		SELECT @txt='Oracle出现分布式事务预留锁：LOCAL_TRAN_ID='+ltrim(LOCAL_TRAN_ID)+'，Fail_Time='+isnull(Fail_Time,'')+'，Host='+isnull(Host,'')+char(13)+char(10)		
			+CASE WHEN Fail_Time IS NOT NULL AND Fail_Time<@dt 
				  THEN '，【将立即回滚该事务！！！】' 
				  ELSE '执行下述sql可解锁：'+char(13)+char(10)
						+'Rollback force '''+ltrim(LOCAL_TRAN_ID)+''';'+char(13)+char(10)
						+'execute dbms_transaction.purge_lost_db_entry('''+ltrim(LOCAL_TRAN_ID)+''');'+char(13)+char(10)
						+'commit;'
			 END
		FROM #pending WHERE ID=@i
		
		INSERT INTO [sy30smoke].[JX_T20_CRM].DBO.tSMSSend(content,mobiletelePhone,typeflag,statusflag,appenddatetime)
			SELECT @txt, t.s, 16 AS typeflag, 0 AS statusflag, getdate() AS appenddatetime
			FROM dbo.[fnSplitString](@phones,',') t
		 
		SET @i=@i+1
	END
	
	SET @i=1
	DECLARE @Fail_Time datetime
	DECLARE @LOCAL_TRAN_ID varchar(100)
	WHILE @i<=(SELECT max(ID) FROM #pending)
	BEGIN
		SELECT @Fail_Time=Fail_Time, @LOCAL_TRAN_ID=''''+ltrim(LOCAL_TRAN_ID)+''''
		FROM #pending WHERE ID=@i
		
		IF @Fail_Time<@dt
		BEGIN
			EXEC('begin
			  pRollbackTransaction(?);
			  commit;
			  end;', @LOCAL_TRAN_ID ) at clinicdb
		END		 
		 
		SET @i=@i+1
	END
END
	
DROP TABLE #tmp
DROP TABLE #pending