oracle数据库需要重构,因此需要删除用户,删除表空间,但是在删除用户过程中,一直提示ORA-01940错误信息,但是经排查没有用户登录,而且在v$session中也没有相关session,记录操作方法。
[oracle@enmoedu1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Fri Apr 17 21:25:40 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> drop user wxc cascade;
drop user wxc cascade
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected
SQL>
查看该用户对应的session
SQL> select username,sid,serial# from v$session where username='WXC';
no rows return
SQL>
如若有相关的session信息,需要将对应session信息删除
SQL> alter system kill session'对应sid,对应serial#';
System altered.
SQL>
删除后再执行删除用户操作drop user wxc cascade。
依然报该错误信息,解决方案如下:
停止数据库服务
SQL > shutdown immediate;
停止监听
lsnrctl stop
然后登陆数据库,再执行删除操作
[oracle@enmoedu1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Fri Apr 17 21:25:40 2015
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> drop user wxc cascade;
成功删除,然后再将监控启动即可lsnrctl start.