ORA-01940: cannot drop a user that is currently connected 问题解决

问题背景

想要重建用户及表空间，删除用户及表空间时报用户已连接
ORA-01940: cannot drop a user that is currently connected
检查一波之后发现实际并没有在使用的，就只能杀除进程了

问题解决

查询用户登录情况并结束相关session

登录删除用户

[oracle@orcldb data]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Jul 12 10:00:44 2022
Version 19.11.0.0.0

Copyright (c) 1982, 2020, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.11.0.0.0

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

查找用户进程，并杀死进程

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

USERNAME
--------------------------------------------------------------------------------
       SID    SERIAL#
---------- ----------
WLPT
      5217	39774


SQL> alter system kill session '5217,39774';

System altered.

删除用户及表空间

SQL> drop user wlpt cascade;

SQL> drop tablespace wlpt_temp INCLUDING CONTENTS AND DATAFILES;

SQL> drop tablespace wlpt_data INCLUDING CONTENTS AND DATAFILES;

重新创建用户及表空间

create temporary tablespace wlpt_temp  
tempfile '/data/wlpt_temp.dbf' 
size 50m  
autoextend on  
next 50m maxsize 20480m  
extent management local;  


create tablespace wlpt_data
logging  
datafile '/data/wlpt_data.dbf' 
size 50m  
autoextend on  
next 50m maxsize 20480m  
extent management local; 


create user wlpt identified by wlpt
default tablespace wlpt_data
temporary tablespace wlpt_temp; 


grant connect,resource,dba to wlpt;