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

谁借莪１个温暖的怀抱￠ 2022-05-27 23:40 139阅读 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。