达梦查询锁表语句oracle,达梦7锁超时查询、解锁脚本

在数据库中经常会碰到对象被锁住的情况，这里分享下我之前整理的以下达梦中锁相关的一些脚本。https://www.cndba.cn/foucus/article/4180

https://www.cndba.cn/foucus/article/4180

–查询活动会话数https://www.cndba.cn/foucus/article/4180

https://www.cndba.cn/foucus/article/4180

select count(*) from v$sessions where state = 'active';

–已经执行超过2s的活动SQLhttps://www.cndba.cn/foucus/article/4180

select * from (

SELECT sess_id,sql_text,datediff(ss,last_recv_time,sysdate) Y_EXETIME,

SF_GET_SESSION_SQL(SESS_ID) fullsql,clnt_ip

FROM V$SESSIONS WHERE STATE='ACTIVE')

where Y_EXETIME>=2;

–锁查询

https://www.cndba.cn/foucus/article/4180https://www.cndba.cn/foucus/article/4180

select o.name,l.* from v$lock l,sysobjects o where l.table_id=o.id and blocked=1;

https://www.cndba.cn/foucus/article/4180

with locks as(

select o.name,l.*,s.sess_id,s.sql_text,s.clnt_ip,s.last_send_time from v$lock l,sysobjects o,v$sessions s

where l.table_id=o.id and l.trx_id=s.trx_id ),

lock_tr as ( select trx_id wt_trxid,row_idx blk_trxid from locks where blocked=1),

res as( select sysdate stattime,t1.name,t1.sess_id wt_sessid,s.wt_trxid,

t2.sess_id blk_sessid,s.blk_trxid,t2.clnt_ip,SF_GET_SESSION_SQL(t1.sess_id) fulsql,

datediff(ss,t1.last_send_time,sysdate) ss,t1.sql_text wt_sql from lock_tr s,locks t1,locks t2

where t1.ltype='OBJECT' and t1.table_id<>0 and t2.ltype='OBJECT' and t2.table_id<>0

and s.wt_trxid=t1.trx_id and s.blk_trxid=t2.trx_id)

select distinct wt_sql,clnt_ip,ss,wt_trxid,blk_trxid from res;

经常用到的两个查询被锁住对象和解锁脚本：

–查看表上的锁是哪个会话导致的https://www.cndba.cn/foucus/article/4180

select a.*,b.NAME,c.SESS_ID from v$lock a

left join sysobjects b on b.ID=a.TABLE_ID

left join v$sessions c on a.TRX_ID=c.TRX_ID;

–kill对应的session

sp_close_session(sess_id);

版权声明：本文为博主原创文章，未经博主允许不得转载。