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_sqlwhere waiter.event=''enq: TX - row lock contention'' and waiter.blocking_session=blocker.sidand w_sql.sql_id=waiter.sql_idand 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;
转载于:https://www.cnblogs.com/iyoume2008/p/5993103.html
相关资源：数据结构—成绩单生成器