文档详情

oracle死锁的处理精要.doc

发布:2017-06-10约7.8千字共10页下载文档
文本预览下载声明
查看有哪些表被锁住 select b.owner,b.object_name,a.session_id,a.locked_mode from v$locked_object a,dba_objects b where b.object_id = a.object_id select b.username,b.sid,b.serial#,logon_time from v$locked_object a,v$session b where a.session_id = b.sid order by b.logon_time 杀进程中的会话 alter system kill session sid,serial#; e.g alter system kill session 29,5497; 如果有ora-00031错误,则在后面加immediate;alter system kill session 29,5497 immediate; oracle死锁进程 .查哪个过程被锁: V$DB_OBJECT_CACHE视图: SELECT * FROM V$DB_OBJECT_CACHE WHERE OWNER=过程的所属用户 AND CLOCKS!=0; 2. 查是哪一个SID,通过SID可知道是哪个SESSION: V$ACCESS视图: SELECT * FROM V$ACCESS WHERE OWNER=过程的所属用户 AND NAME=刚才查到的过程名; 3. 查出SID和SERIAL#: V$SESSION视图: SELECT SID,SERIAL#,PADDR FROM V$SESSION WHERE SID=刚才查到的SID; V$PROCESS视图: SELECT SPID FROM V$PROCESS WHERE ADDR=刚才查到的PADDR; 4. 杀进程: (1)先杀ORACLE进程: ALTER SYSTEM KILL SESSION 查出的SID,查出的SERIAL#; (2)再杀操作系统进程: KILL -9 刚才查出的SPID或ORAKILL 刚才查出的SID 刚才查出的SPID。 Oracle的死锁 select t2.username||  ||t2.sid||  ||t2.serial#||  ||t2.logon_time||  ||t3.sql_text from v$locked_object t1,v$session t2,v$sqltext t3 where t1.session_id=t2.sid and t2.sql_address=t3.address order by t2.logon_time; session了,下面就是杀掉,拿到上面查询出来的SID和SERIAL#,填入到下面的语句中: alter system kill session sid,serial#;   一般情况可以解决数据库存在的死锁了,或通过session id 查到对应的操作系统进程,在Unix中杀掉操作系统的进程。 SELECT a.username,c.spid AS os_process_id,c.pid AS oracle_process_id FROM v$session a,v$process c WHERE c.addr=a.paddr and a.sid= and a.serial#= ; kill (unix) 或 orakill(windows )。 Unix中: ps -ef|grep os_process_id kill -9 os_process_id ps -ef|grep os_process_id Oracle的使用过程中碰到这个问题,所以也总结了一点解决方法。 1)查找死锁的进程: sqlplus /as sysdba  (sys/change_on_install) SELECT s.username,l.OBJECT_ID,l.SESSION_ID,s.SERIAL#, l.ORACLE_USERNAME,l.OS_USER_NAME,l.PROCESS FROM V$LOCKED_OBJECT l,V$SESSION S WHERE l.SESSION_ID=S.SID; 2)kill掉这个死锁的进程: alter system kill session ‘sid,serial#’; (其中sid=l.session_id) 3)如果还不能解决: select pro.spid from v$session ses, v$process pro where ses.sid=XX and ses.paddr=pro.addr; sid用死锁的sid替换: ex
显示全部
相似文档