ORA-01940 无法删除当前已连接的用户之解决方案(脚本)

第一部分：配置数据库连接

1. 安装ODBC

yum -y install unixODBC unixODBC-devel

2. 安装Oracle-instantclient 

#以下所有操作使用root账号执行
#创建目录
mkdir -p /opt/oracle
cd /opt/oracle
#下载odbc安装包
wget https://download.oracle.com/otn_software/linux/instantclient/213000/oracle-instantclient-basic-21.3.0.0.0-1.x86_64.rpm
wget https://download.oracle.com/otn_software/linux/instantclient/213000/oracle-instantclient-sqlplus-21.3.0.0.0-1.x86_64.rpm
wget https://download.oracle.com/otn_software/linux/instantclient/213000/oracle-instantclient-devel-21.3.0.0.0-1.x86_64.rpm
wget https://download.oracle.com/otn_software/linux/instantclient/213000/oracle-instantclient-odbc-21.3.0.0.0-1.x86_64.rpm
#安装unixODBC和unixODBC-devel
yum -y install unixODBC unixODBC-devel
#安装oracle-instantclient
yum localinstall oracle-instantclient-*
#oracle-instantclient的安装位置如下
/usr/lib/oracle/21/client64
/usr/share/oracle/21/client64
/usr/include/oracle/21/client64

3. 配置SQLPlus连接  完颜振江
直接通过下面的命令安装 oracle-instantclient创建 network/admin 文件夹

mkdir /usr/lib/oracle/21/client64/network/admin/ -p
cd /usr/lib/oracle/21/client64/network/admin/
vim tnsnames.ora
 
HOST写oracle数据库服务器IP地址，
SERVICE_NAME写global_name
sqlplus / as sysdba
select global_name from global_name;

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.61.105)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = ORCL)
    )
  )

4. 环境变量配置

export ORACLE_HOME=/usr/lib/oracle/21/client64
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib64:$LD_LIBRARY_PATH
export TNS_ADMIN=$ORACLE_HOME/network/admin
export PATH=$PATH:$ORACLE_HOME/bin:$HOME/bin
export ORACLE_SID=ORCL
 
#保存后适用新的环境变量
source /etc/profile
#配置类库
chmod +x /usr/lib/oracle/21/client64/lib/libsqora.so.21.1
cd /usr/lib64
ln -s libodbcinst.so.2.0.0 libodbcinst.so.1

5.测试连接,出现如下信息表示成功

sqlplus NCC10/XXXXX@ORCL

#sqlplus NCC10/XXXX@ORCL
 
SQL*Plus: Release 21.0.0.0.0 - Production on Tue Nov 15 17:19:33 2022
Version 21.3.0.0.0
 
Copyright (c) 1982, 2021, Oracle.  All rights reserved.
 
Last Successful login time: Tue Nov 15 2022 17:12:14 +08:00
 
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0
 
SQL>

第二部分：脚本

#cat cascade.sh
#!/bin/bash
ID=$1
sqlplus NCC10/XXXX@ORCL > /aliyun/NCC/oracle/Tmp/list << EOF
select username,sid,serial#,paddr from v\$session where username='$ID';
EOF
less /aliyun/NCC/oracle/Tmp/list| grep   [0-9]|awk '{if (NR%2==0) {print} else {printf "%s:",$0}}'|grep -Ev 'Oracle|Release|Version'|awk '{print $1" "$2" "$3}'|sed 's/://g' >/aliyun/NCC/oracle/list
#echo "select username,sid,serial#,paddr from v\$session where username='$ID';";
while read a   b   c
do
	echo "alter system kill session '$b,$c';";
done < /aliyun/NCC/oracle/list
echo "drop  user $ID cascade; ;"

注释：

1.连接并把执行结果输出到/aliyun/NCC/oracle/Tmp/list；sqlplus NCC10/XXXX@ORCL > /aliyun/NCC/oracle/Tmp/list

2.执行的sql语句（可以根据自己的需求调整）select username,sid,serial#,paddr from v\$session where username='$ID';

3、less是对输出文件的处理

执行输出如下

#./cascade.sh NCC10
alter system kill session '25,64344';
alter system kill session '198,42428';
alter system kill session '223,3497';
alter system kill session '402,8434';
alter system kill session '415,11774';
alter system kill session '588,28512';
alter system kill session '602,1934';
alter system kill session '794,46347';
alter system kill session '970,58980';
alter system kill session '980,21601';
drop  user NCC10 cascade; ;

直接多次执行就可以删除需要被删除的oracle的实例

PS：遵循的流程

ORA-01940 无法删除当前已连接的用户之解决方案
 
SQL> drop user ecity ;
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='ECITY';
USERNAME                                    SID    SERIAL# PADDR
 
其次杀掉系统中的这个进程
SQL> alter system kill session '${SID},${SERIAL}';
SQL> select saddr,sid,serial#,paddr,username,status from v$session where username is not null;
 
然后执行删除操作，即可完成
SQL> drop user ecity CASCADE;
User dropped.
 
一直杀不干净咋办（列出所有的SID和SERIAL）和删除用户的命令一起执行
SQL> alter system kill session '${SID},${SERIAL}';
SQL> drop user ecity CASCADE;