create or replace procedure solve_lock_061203(v_msg out varchar2)
as
v_sql varchar2(3000); --定义 v_sql 接受抓取锁的sql语句
kill_sid number;
kill_serial number;
cur_lock sys_refcursor; --定义游标变量，循环执行抓取锁的sql语句
cur_lock02 sys_refcursor;
type tp_lock is record( --定义 record类型的 变量
v_sid number,
v_type varchar2(10),
v_id1 number,
v_id2 number,
v_lmode varchar2(200),
v_request varchar2(200),
v_lock_time number,
v_block number);
records_lock tp_lock;
type tp_lock02 is record(
waiting_sid number,
waiting_sql varchar2(1000),
blocker_event varchar2(1000),
blocking_sid number,
blocking_sql varchar2(1000));
records_lock02 tp_lock02;
v_blocking_sid number;
v_waiting_sid number;
begin
v_msg := '';
v_sql := '';
v_msg := v_msg ||
'------------------查找数据库中是否有锁阻塞、锁等待的情况------------------' ||
chr(10);
-- DBMS_OUTPUT.PUT_LINE('------------------查找数据库中是否有锁阻塞、锁等待的情况------------------');
v_sql := 'select sid,type,id1,id2,
decode(lmode,0, ''none'',1,''null'', 2, ''row share'' ,3, ''row exclusive'' ,4, ''share'' ,5, ''share row exclusive'' ,6 ,''exclusive'' ,''null'') lock_type,
decode(request,0, ''none'',1,''null'', 2, ''row share'' ,3, ''row exclusive'' ,4, ''share'' ,5, ''share row exclusive'' ,6 ,''exclusive'' ,''hull'') lock_request,
ctime,block from v$lock where type in (''TM'',''TX'')';
open cur_lock for v_sql;
loop
fetch cur_lock
into records_lock;
exit when cur_lock%notfound;
if records_lock.v_request <> 'none' then
--抓取发出请求锁的会话
v_msg := v_msg || 'waiting sid: ' || records_lock.v_sid ||
' is request a lock ,lock_mode is ' ||
records_lock.v_request || ' and being locked ' ||
records_lock.v_lock_time || 's' || chr(10);
--dbms_output.put_line('waiting sid: '||records_lock.v_sid||' is request a lock ,lock_mode is '||records_lock.v_request||' and being locked '|| records_lock.v_lock_time||'s');
v_waiting_sid := records_lock.v_sid;
end if;
if records_lock.v_block <> 0 then
--抓取发生锁阻塞的会话
v_msg := v_msg || 'blocking sid: ' || records_lock.v_sid ||
' is make a lock , lock_mode is ' || records_lock.v_lmode ||
chr(10);
/* dbms_output.put_line('blocking sid: ' || records_lock.v_sid ||
' is make a lock , lock_mode is ' ||
records_lock.v_lmode);*/
v_blocking_sid := records_lock.v_sid;
end if;
end loop;
close cur_lock;
v_sql := '';
v_msg := v_msg ||
'------------------查找产生锁的会话、锁等待的会话------------------------' ||
chr(10);
-- dbms_output.put_line('------------------查找产生锁的会话、锁等待的会话------------------------');
v_sql := 'select distinct waiter.sid "waiting_sid",w_sql.sql_text "sql from waiting session",blocker.event "blocker event",blocker.sid "blocking sid",
b_sql.sql_text "sql from blocking session" from v$session waiter, v$session blocker,v$sql w_sql,v$sql b_sql
where waiter.event=''enq: TX - row lock contention'' and waiter.blocking_session=blocker.sid
and w_sql.sql_id=waiter.sql_id
and b_sql.sql_id =nvl(blocker.sql_id,blocker.prev_sql_id)';
open cur_lock02 for v_sql;
loop
fetch cur_lock02
into records_lock02;
exit when cur_lock02%notfound;
if records_lock02.waiting_sid is not null then
v_msg := v_msg || 'waiting sid: ' || records_lock02.waiting_sid ||
' wait_sql is : ' || records_lock02.waiting_sql || chr(10);
/* dbms_output.put_line('waiting sid: ' || records_lock02.waiting_sid ||
' wait_sql is : ' || records_lock02.waiting_sql);*/
end if;
if records_lock02.blocking_sid is not null then
v_msg := v_msg || 'blocking sid: ' || records_lock02.blocking_sid ||
' block_sql is : ' || records_lock02.blocking_sql || chr(10);
/* dbms_output.put_line('blocking sid: ' || records_lock02.blocking_sid ||
' block_sql is : ' ||
records_lock02.blocking_sql);*/
end if;
end loop;
close cur_lock02;
v_msg := v_msg || '------------------解决 锁阻塞、锁等待------------------' ||
chr(10);
-- dbms_output.put_line('------------------解决 锁阻塞、锁等待------------------');
select sid, serial#
into kill_sid, kill_serial
from v$session
where sid = v_blocking_sid;
v_msg := v_msg || 'action: alter system kill session ( ' || kill_sid || ',' ||
kill_serial || ')' || chr(10);
/* dbms_output.put_line('action: alter system kill session ( ' || kill_sid || ',' ||
kill_serial || ')');*/
exception
when no_data_found then
v_msg := v_msg || sqlerrm;
/* dbms_output.put_line(sqlcode || sqlerrm);*/
end solve_lock_061203;