本文介绍了如何使用Oracle数据库内置视图解决锁表问题。主要内容包括：以sys用户身份登录并查询被锁定的会话；根据SID查询具体SQL语句；记录查询到的SQL并终止锁定会话。
解决锁表
<wbr>1.以sys 用户登入查询哪些session 被锁<br> sql>select t2.username,t2.sid,t2.serial#,t2.logon_time<br><wbr><wbr><wbr> from v$locked_object t1,v$session t2<br><wbr><wbr><wbr> where t1.session_id=t2.sid order by t2.logon_time;<br><br> 2.根据sid查询该会话的具体sql语句<br> sql>select sql_text<br><wbr><wbr><wbr> from v$session a ,v$sqltext_with_newlines b<br><wbr><wbr><wbr><wbr> where<br><wbr><wbr><wbr> DECODE (a.sql_hash_value,0 ,prev_hash_value,sql_hash_value)= b.hash_value<wbr><wbr><br><wbr><wbr><wbr> and a.sid=&sid order by piece;<br><wbr><wbr><wbr><br><br><br> 3.可以记录 《2》中查询的sql语句，然后kill 该会话。<br><br><wbr> sql>alter system kill session '512,2186';<br><br></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
<wbr>1.以sys 用户登入查询哪些session 被锁<br> sql>select t2.username,t2.sid,t2.serial#,t2.logon_time<br><wbr><wbr><wbr> from v$locked_object t1,v$session t2<br><wbr><wbr><wbr> where t1.session_id=t2.sid order by t2.logon_time;<br><br> 2.根据sid查询该会话的具体sql语句<br> sql>select sql_text<br><wbr><wbr><wbr> from v$session a ,v$sqltext_with_newlines b<br><wbr><wbr><wbr><wbr> where<br><wbr><wbr><wbr> DECODE (a.sql_hash_value,0 ,prev_hash_value,sql_hash_value)= b.hash_value<wbr><wbr><br><wbr><wbr><wbr> and a.sid=&sid order by piece;<br><wbr><wbr><wbr><br><br><br> 3.可以记录 《2》中查询的sql语句，然后kill 该会话。<br><br><wbr> sql>alter system kill session '512,2186';<br><br></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr></wbr>
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
