达梦数据库会话锁处理

达梦数据库会话锁处理

1、查找锁
select s.user_name,s.clnt_ip,s.sql_text,s.sess_id from v$lockl,v$sessions s where l.trx_id=s.trx_id;
2、踢出单条锁语句
SP_CLOSE_SESSION(SESSION_ID)
3、踢出所有数据库连接
1）方式一：
begin
for rec in (SELECT SESS_ID FROM V$SESSIONS WHERE SESS_ID !=SESSID)
loop
sp_close_session(rec.sess_id);
END LOOP;
sp_close_session(SESSID);
end
/
2）方式二：
select ‘sp_close_session(’||sess_id||’); ’ from v$sessions;
4、排除死锁和阻塞：
–查询视图
select count(*) from v$sessions where state=‘ACTIVE’;
select
sysdate ,
SF_GET_SESSION_SQL(SESS_ID), --获取完整sql
sess_id ,
sess_seq ,
sql_text ,
state ,
seq_no ,
user_name ,
trx_id ,
create_time ,
clnt_ip
from v$sessions
where state=‘ACTIVE’;
–锁查询
select o.name, l.*
from v$lock l, sysobjects o
where l.table_id=o.id and blocked =1;
with locks as
( select o.name ,
l.* ,
s.sess_id ,
s.sql_text,
s.clnt_ip
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_trid, row_idx blk_trid from locks where blocked=1
)
,
res as
(
select
sysdate stattime ,
t1.name ,
t1.sess_id wt_sessid ,
s.wt_trid ,
t1.sql_text wt_sql ,
t2.sess_id blk_sessid,
s.blk_trid ,
t2.clnt_ip
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_trid =t1.trx_id
and s.blk_trid =t2.trx_id
)
select * from res;
–已执行未提交的sql
select
t1.sql_text,
t1.state ,
t1.trx_id
from
v$sessionst1,v$trx t2
where
t1.trx_id=t2.id
and t1.state =‘IDLE’
and t2.status=‘ACTIVE’;
–查询有事务未提交的表
select b.object_name, c.sess_id , a.* from
v$lock a ,
dba_objects b,
v$sessions c
where
a.table_id=b.object_id
and ltype =‘OBJECT’
and a.trx_id =c.trx_id;
–查询长时间的sql
select * from
(
SELECT
sess_id ,
sql_text ,
datediff(ss, last_send_time, sysdate) ss,
SF_GET_SESSION_SQL(SESS_ID) fullsql
FROM
V$SESSIONS
WHERE
STATE=‘ACTIVE’
)
where ss>=2;