ORA-00030: User session ID does not exist.

---

前言

在drop用户时候出现 ORA-01940: cannot drop a user that is currently connected 通过sid，serial#去kill进程遇到ORA-00030: User session ID does not exist 发现serial#的值一直在变化，无法定位kill 处理思路： 1、通过sid找到相应的spid 2、系统层面kill spid

---

提示：以下是本篇文章正文内容，下面案例可供参考

一、问题报错展示

select username,sid,serial#,paddr from v$session where username='UM';

USERNAME	  SID	 SERIAL# PADDR
---------- ---------- ---------- ----------------
UM		    3	   44956 0000000270EEDC38
UM		 1417	   29437 0000000270F01DC8
UM		 2836	   29259 00000002B0DA9268
UM		 5031	   33265 0000000250DAE5D8
UM		 7383	   26237 0000000280D94210

SQL> alter system kill session'3,44956';
alter system kill session'3,44956'
*
ERROR at line 1:
ORA-00030: User session ID does not exist.


SQL> select username,sid,serial#,paddr from v$session where username='UM';

USERNAME	  SID	 SERIAL# PADDR
---------- ---------- ---------- ----------------
UM		    3	   19840 0000000270EEDC38
UM		 1417	   29437 0000000270F01DC8
UM		 2836	   29259 00000002B0DA9268
UM		 5031	   33265 0000000250DAE5D8
UM		 7383	   37536 0000000280D94210

二、通过sid找到对应spid，系统层面kill进程

SQL> select spid from v$process where addr in (select paddr from v$session where sid in ('3','1417','2836','5031','7383'));

SPID
------------------------------------------------------------------------
222805
55427
111815
378956
222803

SQL> ! kill -9 222805

SQL> ! kill -9 55427

SQL> ! kill -9 111815

SQL> ! kill -9 378956
/bin/bash: line 0: kill: (378956) - No such process

SQL> ! kill -9 222803

SQL>  select username,sid,serial#,paddr from v$session where username='UM';

USERNAME	  SID	 SERIAL# PADDR
---------- ---------- ---------- ----------------
UM		 1417	   29437 0000000270F01DC8
UM		 2836	   29259 00000002B0DA9268
SQL> select spid from v$process where addr in (select paddr from v$session where sid in ('1417','2836'));

no rows selected

SQL> 
SQL> select username,sid,serial#,paddr from v$session where username='UM';

no rows selected

SQL> 
SQL> 
SQL> drop user UM cascade;
drop user UM cascade
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected

注意：如果是集群模式，注意多节点的进程占用

SQL> select username,sid,serial#,paddr from v$session where username='UM';

USERNAME	  SID	 SERIAL# PADDR
---------- ---------- ---------- ----------------
UM		 2522	   38989 0000000260F03338
UM		 6442	   21710 0000000260EEC6C8

SQL> 
SQL> 
SQL> alter system kill session '2522,38989';

System altered.

SQL> alter system kill session '6442,21710';

System altered.
SQL> drop user UM cascade;

User dropped.