ORA-01940: cannot drop a user that is currently connected 解决方案和 TNS-12537: TNS:connection closed 解决方

最新推荐文章于 2022-08-03 21:23:24 发布

Koma-forever

最新推荐文章于 2022-08-03 21:23:24 发布

阅读量1.3k

点赞数

分类专栏： oracle 文章标签： oracle

本文链接：https://blog.csdn.net/www1056481167/article/details/79694862

版权

oracle 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

`ORA-01940:cannot drop a user that is currently connected`

解决方案
ORA-01940: cannot drop a user that is currently connected

SQL> conn /as sysdba
Connected.
SQL> drop user test cascade;
drop user test cascade
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected
通过查看用户的进行，并kill用户进程，然后删除用户
SQL> select sid,serial# from v$session where username='test';
 
no rows selected
 
SQL> select sid,serial# from v$session where username='TEST';
 
       SID    SERIAL#
---------- ----------
       150       9019
 
SQL> alter system kill session '150,9019';
 
System altered.

TNS-12537:TNS:connection closed

listener 无监听，无法连接数据库
LINUX登录oracle用户，通过lsnrctl start命令启动数据库时，报错如下：

“ [oracle@localhost ~]$ lsnrctl start

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 29-APR-2015 
07:38:11

Copyright (c) 1991, 2009, Oracle. All rights reserved.

Starting /home/oracle/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: 
please wait…

TNS-12537: TNS:connection closed 
TNS-12560: TNS:protocol adapter error 
Linux Error: 29: Illegal seek
原因： 
/etc/hosts内有两个localhost的主机名，监听冲突。
解决方法: 
1、修改其中一个主机名，比如oracle，执行以下命令。
hostname oracle
2、在/etc/hosts内修改一个主机名为oracle。
[root@oracle etc]# cat /etc/hosts 
127.0.0.1 localhost 
::1 oracle
3、在/etc/sysconfig/network修改主机名为oracle。
[root@oracle etc]# cat /etc/sysconfig/network 
NETWORKING=yes 
HOSTNAME=oracle
4、重新启动，在oracle用户下执行lsnrctl start。