Oracle定位对阻塞的对象或锁信息
2022/1/13 19:03:56
本文主要是介绍Oracle定位对阻塞的对象或锁信息,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
set serveroutput on size unlimited set feedback off DECLARE v_num_sessions INTEGER := 0; CURSOR cv IS SELECT dba_objects.object_name, locks_t.row#, locks_t.blocked_secs, locks_t.blocker_text, locks_t.blocked_text, locks_t.blocked_sql_text FROM (SELECT /*+ NO_MERGE */ blocking_lock_session.username||'@'||blocking_lock_session.machine||'(SID='||blocking_lock_session.sid||') ['|| blocking_lock_session.program||'/PID='||blocking_lock_session.process||']' as blocker_text, blocked_lock_session.username||'@'||blocked_lock_session.machine|| '(SID='||blocked_lock_session.sid||') ['|| blocked_lock_session.program||'/PID='||blocked_lock_session.process||']' as blocked_text, blocked_lock_session.row_wait_obj#, blocked_lock_session.row_wait_file#, blocked_lock_session.row_wait_block#, blocked_lock_session.row_wait_row#, DBMS_ROWID.ROWID_CREATE (1, blocked_lock_session.row_wait_obj#, blocked_lock_session.row_wait_file#, blocked_lock_session.row_wait_block#, blocked_lock_session.row_wait_row#) row#, blocked_lock_session.seconds_in_wait blocked_secs, blocked_sql.sql_text blocked_sql_text FROM v$lock blocking_lock, v$session blocking_lock_session, v$lock blocked_lock, v$session blocked_lock_session, v$sql blocked_sql WHERE blocking_lock.block = 1 AND blocking_lock.id1 = blocked_lock.id1 AND blocking_lock.id2 = blocked_lock.id2 AND blocked_lock.request > 0 AND blocking_lock.sid = blocking_lock_session.sid AND blocked_lock.sid = blocked_lock_session.sid AND blocked_lock_session.sql_id = blocked_sql.sql_id AND blocked_lock_session.sql_child_number = blocked_sql.child_number ) locks_t, dba_objects WHERE locks_t.row_wait_obj# = dba_objects.object_id AND locks_t.blocked_secs > &1 ORDER BY locks_t.blocked_secs; BEGIN FOR cv_rec IN cv LOOP dbms_output.put_line( '========= $Revision: 1.4 $ ($Date: 2013/09/16 13:15:22 $) ==========='); v_num_sessions := v_num_sessions + 1; dbms_output.put_line('Locked object : '|| cv_rec.object_name); dbms_output.put_line('Locked row# : '|| cv_rec.row#); dbms_output.put_line('Blocked for : '|| cv_rec.blocked_secs||' seconds'); dbms_output.put_line('Blocker info. : '|| cv_rec.blocker_text); dbms_output.put_line('Blocked info. : '|| cv_rec.blocked_text); dbms_output.put_line('Blocked SQL : '|| cv_rec.blocked_sql_text); END LOOP; dbms_output.new_line; dbms_output.put_line('Found '||TO_CHAR(v_num_sessions)|| ' blocked session(s).'); END; / exit;
上面这个语句用来查当前被阻塞的对象详细信息,特别好使
根据上面的结果 知道了阻塞对象、阻塞数据的rowid,阻塞者和被阻塞者的SID
根据阻塞者的SID 找到对应对应和serial#
select b.sid,b.serial#,b.username,a.sql_text
from v$sqlarea a,v$session b where a.SQL_ID=b.PREV_SQL_ID and b.SID=14;
然后kill掉这个进程,阻塞被释放
alter system kill session '14,16293'
这篇关于Oracle定位对阻塞的对象或锁信息的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-11-23增量更新怎么做?-icode9专业技术文章分享
- 2024-11-23压缩包加密方案有哪些?-icode9专业技术文章分享
- 2024-11-23用shell怎么写一个开机时自动同步远程仓库的代码?-icode9专业技术文章分享
- 2024-11-23webman可以同步自己的仓库吗?-icode9专业技术文章分享
- 2024-11-23在 Webman 中怎么判断是否有某命令进程正在运行?-icode9专业技术文章分享
- 2024-11-23如何重置new Swiper?-icode9专业技术文章分享
- 2024-11-23oss直传有什么好处?-icode9专业技术文章分享
- 2024-11-23如何将oss直传封装成一个组件在其他页面调用时都可以使用?-icode9专业技术文章分享
- 2024-11-23怎么使用laravel 11在代码里获取路由列表?-icode9专业技术文章分享
- 2024-11-22怎么实现ansible playbook 备份代码中命名包含时间戳功能?-icode9专业技术文章分享