【ORA-00031】标记要终止的会话

有个表锁了，执行alter system kill session ‘770,17939’后，报‘ora-00031’错误，有两种方法，一种是在后面加immediate，另一种见下面的详细方案：

错误截图：

原因： 在ORACLE客户端中的进程被杀掉后，状态被置为”killed”，但是锁定的资源很长时间不释放。

解决方案：

方法一：重启数据库服务。由于数据库中有“killded”状态的进程，一直杀不掉，
shutdown immediate命令可能会一直停留在shutdown immediate过程，
而使用shutdown abort 会损坏数据库。
方法二：在数据库服务器层面杀掉“killded”状态的进程。

方案二流程步骤见下面内容：

1. 查看锁表：

2. 查看状态为”killded”的进程,查询结果要和上面锁表的内容对应。

3. 查询对应进程在数据库服务器（操作系统）的进程号
SID为770来源于上述的SID

4. 在数据库服务器上杀进程

1）在unix上，用root身份执行命令：#kill -9 26830（spid 由上述2.的查询）
2) 在windows（unix也适用）用orakill杀死线程，orakill是oracle提供的一个可执行命令，sid是要杀死的进程属于的实例,thread是要杀掉的线程号名。(注意：进程是在服务端操作，不能在客户端执行)

语法为： orakill sid thread

orakill 770 26830

表锁的原因及解决步骤：

特别注意
1. 先通过 ADDR 寻找会话对应的操作系统的 SPID（System Process ID）
2. 再使用 KILL SESSION -9 SPID 命令杀掉操作系统的进程 ID
3. 谨慎操作 -9 命令，很可能会产生意想不到的后果

数据库执行语句的分类
DML：数据操纵语言，关键字：INSERT、UPDATE、DELETE
DCL：数据库控制语言，关键字：GRANT、REVOKE
DQL：数据库查询语言，关键字：SELECT … FROM … WHERE
DDL：数据库模式定义语言，关键字：CREATE，DROP，ALTER
TCL：事务控制语言，关键字：COMMIT、ROLLBACK、SAVEPOINT

oracle表锁表的情况如下：

DML锁又可以分为，行锁、表锁、死锁

行锁：
当事务执行数据库插入、更新、删除操作时，该事务自动获得操作表中操作行的排它锁。

表级锁：
当事务获得行锁后，此事务也将自动获得该行的表锁(共享锁),以防止其它事务进行DDL语句影响记录行的更新。事务也可以在进行过程中获得共享锁或排它锁，只有当事务显示使用LOCK TABLE语句显示的定义一个排它锁时，事务才会获得表上的排它锁,也可使用LOCK TABLE显示的定义一个表级的共享锁(LOCK TABLE具体用法请参考相关文档)。

死锁：
当两个事务需要一组有冲突的锁，而不能将事务继续下去的话，就出现死锁。
如事务1在表A行记录#3中有一排它锁，并等待事务2在表A中记录#4中排它锁的释放，而事务2在表A记录行#4中有一排它锁，并等待事务1在表A中记录#3中排它锁的释放，事务1与事务2彼此等待，因此就造成了死锁。死锁一般是因拙劣的事务设计而产生。
死锁只能使用SQL下:alter system kill session “sid,serial#”；或者使用相关操作系统kill进程的命令，如UNIX下kill -9 sid,或者使用其它工具杀掉死锁进程。

DDL锁又可以分为：排它DDL锁、共享DDL锁、分析锁

排它DDL锁：
创建、修改、删除一个数据库对象的DDL语句获得操作对象的排它锁。如使用alter table语句时，为了维护数据的完成性、一致性、合法性，该事务获得一排它DDL锁。

共享DDL锁：
需在数据库对象之间建立相互依赖关系的DDL语句通常需共享获得DDL锁。
如创建一个包，该包中的过程与函数引用了不同的数据库表，当编译此包时，该事务就获得了引用表的共享DDL锁。

分析锁：
ORACLE使用共享池存储分析与优化过的SQL语句及PL/SQL程序，使运行相同语句的应用速度更快。一个在共享池中缓存的对象获得它所引用数据库对象的分析锁。分析锁是一种独特的DDL锁类型，ORACLE使用它追踪共享池对象及它所引用数据库对象之间的依赖关系。当一个事务修改或删除了共享池持有分析锁的数据库对象时，ORACLE使共享池中的对象作废，下次在引用这条SQL/PLSQL语句时，ORACLE重新分析编译此语句。

查询锁表的几个相关表
SELECT * FROM v$lock;

SELECT * FROM v$sqlarea;

SELECT * FROM v$session;

SELECT * FROM v$process ;

SELECT * FROM v$locked_object;

SELECT * FROM all_objects;

SELECT * FROM v$session_wait;

(1)锁表查询的代码有以下的形式：

select count(*) from v$locked_object;

select * from v$locked_object;

SELECT a.sid, b.owner, object_name, object_type FROM v$lock a, all_objects b WHERE TYPE = ‘TM’ and a.id1 = b.object_id;

select * from v $locked_object lo,dba_objects ao, v$ session sess where ao.object_id = lo.object_id and lo.session_id = sess.sid;

查询killded状态的进程，结果与查锁sql对应
select a.spid,b.sid,b.serial#,b.username from v $p rocess a, v$ session b where a.addr=b.paddr and b.status=‘KILLED’;

找到对应的进行，查询对应进程在数据库服务器（操作系统）的进程号， – 替换成相应的session_id
select b.spid,a.osuser,b.program from v $sess i o na, v$ process b where a.paddr=b.addr and a.sid=770

(2)查看哪个表被锁

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 A.OWNER 方案名,A.OBJECT_NAME 表名,B.XIDUSN 回滚段号,B.XIDSLOT 槽号,B.XIDSQN 序列号,
B.SESSION_ID 锁表SESSION_ID,B.ORACLE_USERNAME 锁表用户名,
decode(D.type,‘XR’,‘NULL’,‘RS’,‘SS(Row-S)’,‘CF’,‘SS(Row-S)’, ‘TM’, ‘TABLE LOCK’, ‘PW’, ‘TABLE LOCK’, ‘TO’, ‘TABLE LOCK’, ‘TS’, ‘TABLE LOCK’, ‘RT’, ‘ROW LOCK’, ‘TX’, ‘ROW LOCK’,‘MR’,‘S(Share)’,NULL) 锁定方式,
C.MACHINE 用户组,C.TERMINAL 机器名,B.OS_USER_NAME 系统用户名,B.PROCESS 系统进程id,
DECODE(C.STATUS,‘INACTIVE’,‘不活动’,‘ACTIVE’,‘活动’) 活动情况 ,
C.SERVER,C.SID,C.SERIAL#,C.PROGRAM 连接方式,C.LOGON_TIME
FROM ALL_OBJECTS A,
V $KaTeX parse error: Expected group after ‘_’ at position 24: \dotsBJECT B, SYS.GV_̲$ SESSION C,
v$lock d
WHERE ( A.OBJECT_ID = B.OBJECT_ID )
AND (B.PROCESS = C.PROCESS )
and C.sid = d.sid
and B.LOCKED_MODE = D.LMODE
AND B.SESSION_ID = ‘770’
ORDER BY 1,2;

–
select p.spid,a.serial#, c.object_name,b.session_id,b.oracle_username,b.os_user_name
from v $p rocess p, v$ session a,v$locked_object b,all_objects c
where p.addr=a.paddr and a.process=b.process and c.object_id=b.object_id and b.session_id = 770;

(3)查看是哪个session引起的 – 哪个用户哪个进程造成死锁

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;

查看连接的进程
SELECT sid, serial#, username, osuser FROM v$session;

查出锁定表的sid, serial#,os_user_name, machine_name, terminal，锁的type,mode
SELECT s.sid, s.serial#, s.username, s.schemaname, s.osuser, s.process, s.machine,s.terminal, s.logon_time, l.type
FROM v $sess i o n s, v$ lock l WHERE s.sid = l.sid AND s.username IS NOT NULL AND s.sid = 770 ORDER BY sid;

(4)杀掉对应进程

-执行命令：
alter system kill session ‘770,17939’ immediate;
alter system kill session’770,17939’; – 其中770 为sid, 17939 为 serial#.
alter system kill session’770,41839’; – 其中770 为sid, 41839 为 serial#.

查询被锁资源的sid 、serial#
SELECT s.sid,s.serial#,v.,ao. FROM v $locked_object v,all_objects ao,v$ session s WHERE v.object_id = ao.object_id AND s.sid = v.session_id;

如果session经常性的过期,不过几分种就过期,是因为你的程充中占用了太多的资源,
超过了iis中应用配置的资源占用大小,iis中asp_iis进程进行自动重启,所以就session过期了,
建议检查一下你的代码,是否占用cpu在某些程序代码里面很高,或者占用内存相当的高

获取 kill session 信息
SELECT s.inst_id,
s.sid,
s.serial#,
p.spid,
s.username,
s.program,
s.paddr,
s.STATUS
FROM gv $sess i o n s J O I N gv$ process p ON p.addr = s.paddr AND p.inst_id = s.inst_id
WHERE s.type != ‘BACKGROUND’
AND s.serial# = ‘17939’ – spid = 26830
;

杀掉会话
ALTER SYSTEM KILL SESSION ‘770,17939’ IMMEDIATE;

多会话 kill session 的应用场景
select ‘alter system kill session ‘’’|| sid ||‘,’ ||SERIAL# ||‘’‘’||‘;’ from gv$session
where sid in (‘770’)
order by inst_id;

ALTER SYSTEM KILL SESSION ‘770,17939’;

出现ORA-00031则在后面添加immediate

ALTER SYSTEM KILL SESSION ‘770,17939’ immediate;

操作系统级别杀掉会话

SELECT SPID FROM v$process WHERE ADDR IN (‘000000035EC8F218’);
KILL SESSION -9 ‘26830’; – SPID

–
SELECT * FROM v$process where SPID = ‘26830’
KILL SESSION -9 ‘26830’;

【ORA-00031】标记要终止的会话