公司一套产品3期马上要上线,被临时调到开发部门,下午刚刚到开发部门报到,就遇到SIT环境表空间不足,增加数据文件后,开发人员说数据库遇到死锁(数据库版本10.2.0.5.0 64 bit for linux，单实例),检查告警日志并没有发现60错误,经查询,只是delete操作引起的锁表,并不是死锁。

SQL> select 'alter system kill session '''||sess.sid||','||sess.serial#||''';' KSQL,sa.SQL_FULLTEXT
  2  from v$locked_object lo,dba_objects ao,v$session sess,v$sqlarea sa
  3  where ao.object_id = lo.object_id and lo.session_id = sess.sid AND sess.SQL_ADDRESS=sa.ADDRESS
  4  AND sess.SQL_HASH_VALUE=sa.HASH_VALUE ORDER BY sa.SQL_TEXT;

KSQL                                     SQL_FULLTEXT
-------------------------------------    ------------------------------------------------------------------------
alter system kill session '1544,271';    delete from tms_t_hub.qrtz_req_data_logs t where t.add_time<to_date('2015-06-02','yyyy-mm-dd')

开发人员说这个delete操作是他在PLSQL Developer上操作的,断网之后再次操作提示表被锁了,让我直接kill掉,在kill这个session的时候,遇到了ORA-00031错误。

SQL> alter system kill session '1544,271';
alter system kill session '1544,271'
*
ERROR at line 1:
ORA-00031: session marked for kill

这个错误是这个session已经被杀掉了，再问开发人员他说他已经执行过这个操作了，只是表还有锁，delete操作仍然无法进行，这或许就是他说的死锁吧。检查发现这个session已经是killed状态了。

SQL> select SADDR,PADDR,username ,status from v$session where sid=1544 and serial#=271;

SADDR            PADDR            USERNAME                       STATUS
---------------- ---------------- ------------------------------ --------
00000005564D8280 0000000556344898 TMS_T_USER                     KILLED

此时锁依然存在，对于这种在数据库无法杀掉的session，最有效的方法就是在操作系统上暴力干掉，根据session信息，查到这个session对应的操心系统进程号。

SQL> select PADDR,username from v$session where sid=1544 and serial#=271;

PADDR            USERNAME
---------------- ------------------------------
0000000556344898 TMS_T_USER

SQL> select spid from v$process where ADDR='0000000556344898';

SPID
------------
24929

在操作系统上查到进程号为24929的进程。

[oracle@SL010A-ISITDB8 ~]$ ps -ef | grep 24929
oracle   24929     1  3 Jun02 ?        00:37:17 oracleTMS (LOCAL=NO)
oracle   30581 30539  0 14:57 pts/0    00:00:00 grep 24929

LOCAL=NO表示这个进程是远程通过tns链接到数据库的，也就是非核心进程，干掉并不会对实例造成 影响，此步骤也是检验进程号是否写错或者查错，进程一旦杀错，就要看RP值是否爆棚了。

[oracle@SL010A-ISITDB8 ~]$ kill -9 24929
[oracle@SL010A-ISITDB8 ~]$ ps -ef | grep 24929
oracle   30657 30539  0 15:04 pts/0    00:00:00 grep 24929

杀完后，再次查询，session已经释放，delete操作可正常进行。

SQL> select PADDR,username,status from v$session where sid=1544 and serial#=271;

no rows selected

很久没有跟开发团队了，在开发和测试阶段，遇到锁的情况还多的。

本文固定链接: https://www.dbdream.com.cn/2015/06/oracle%e6%95%b0%e6%8d%ae%e5%ba%93%e9%94%81%e8%a1%a8%e5%8f%8aora-00031%e9%94%99%e8%af%af/ | 信春哥，系统稳，闭眼上线不回滚！