由中间件连接引起的ora-01940错误的解决

最新推荐文章于 2024-06-06 17:21:44 发布
最新推荐文章于 2024-06-06 17:21:44 发布
阅读量1.3k 收藏
点赞数
分类专栏： sql与plsql 文章标签： oracle weblogic kill session plsql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/lmalds/article/details/30066033
版权

最近经常用数据泵在生产库与测试库间传递数据，生厂上expdp出来的dmp文件应用在测试库前，需要先将测试库上的已有用户删除或删除用户下的对象。

我一般都是先将用户删除，然后再导入。删除时，经常报出ora-01940:无法删除当前已连接用户。此时，需要查出哪些session以此用户连接，然后kill掉即可。

对于中间件的连接（weblogic、tomcat、jboss），方法是一样的，只不过中间件的session数量比较大，一个一个kill太过漫长，此时，可以通过pl/sql代码块将session杀掉即可。

整个过程如下：

（1）：查看版本，导入前删除用户，报出ora-01940

SQL> select * from v$version where rownum<=1;

BANNER                                                                          
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production    

SQL> drop user sipo132 cascade;
drop user sipo132 cascade
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected
（2）：查看当前连接到sipo132的session，<span style="font-family: SimSun; font-size: 18px; ">发现有100个session在连接，且通过machine和program确定为中间件在启动时通过连接池建立的100个长连接。</span>
SQL> col osuser for a10
SQL> col machine for a20
SQL> col program for a20
SQL> select sid,serial# sn,osuser,machine,program
  2  from v$session
  3  where username='SIPO132';

       SID         SN OSUSER     MACHINE              PROGRAM                   
---------- ---------- ---------- -------------------- --------------------      
       300       3353 weblogic   nw-9000-06           JDBC Thin Client          
       301       5472 weblogic   nw-9000-06           JDBC Thin Client          
       302      19132 weblogic   nw-9000-06           JDBC Thin Client          
       303      43181 weblogic   nw-9000-06           JDBC Thin Client          
       306      30606 weblogic   nw-9000-06           JDBC Thin Client          
       311      62464 weblogic   nw-9000-06           JDBC Thin Client          
       312      64812 weblogic   nw-9000-06           JDBC Thin Client          
       315      35657 weblogic   nw-9000-06           JDBC Thin Client          
       317       6786 weblogic   nw-9000-06           JDBC Thin Client          
       318      20600 weblogic   nw-9000-06           JDBC Thin Client          
       319      48894 weblogic   nw-9000-06           JDBC Thin Client          

       SID         SN OSUSER     MACHINE              PROGRAM                   
---------- ---------- ---------- -------------------- --------------------      
       320      42020 weblogic   nw-9000-06           JDBC Thin Client          
       321      15552 weblogic   nw-9000-06           JDBC Thin Client          
       323      12383 weblogic   nw-9000-06           JDBC Thin Client          
       324         57 weblogic   nw-9000-06           JDBC Thin Client          
       325         42 weblogic   nw-9000-06           JDBC Thin Client          
       332         57 weblogic   nw-9000-06           JDBC Thin Client          
       333         43 weblogic   nw-9000-06           JDBC Thin Client          
       334         48 weblogic   nw-9000-06           JDBC Thin Client          
       335         48 weblogic   nw-9000-06           JDBC Thin Client          
       337         67 weblogic   nw-9000-06           JDBC Thin Client          
       339         51 weblogic   nw-9000-06           JDBC Thin Client          

       SID         SN OSUSER     MACHINE              PROGRAM                   
---------- ---------- ---------- -------------------- --------------------      
       340        121 weblogic   nw-9000-06           JDBC Thin Client          
       341         63 weblogic   nw-9000-06           JDBC Thin Client          
       342         57 weblogic   nw-9000-06           JDBC Thin Client          
       343         41 weblogic   nw-9000-06           JDBC Thin Client          
       344         50 weblogic   nw-9000-06           JDBC Thin Client          
       346         51 weblogic   nw-9000-06           JDBC Thin Client          
       347         51 weblogic   nw-9000-06           JDBC Thin Client          
       348         58 weblogic   nw-9000-06           JDBC Thin Client          
       356         43 weblogic   nw-9000-06           JDBC Thin Client          
       358         50 weblogic   nw-9000-06           JDBC Thin Client          
       359         43 weblogic   nw-9000-06           JDBC Thin Client          

       SID         SN OSUSER     MACHINE              PROGRAM                   
---------- ---------- ---------- -------------------- --------------------      
       360         54 weblogic   nw-9000-06           JDBC Thin Client          
       361         46 weblogic   nw-9000-06           JDBC Thin Client          
       362         52 weblogic   nw-9000-06           JDBC Thin Client          
       363         56 weblogic   nw-9000-06           JDBC Thin Client          
       364         41 weblogic   nw-9000-06           JDBC Thin Client          
       365         59 weblogic   nw-9000-06           JDBC Thin Client          
       366      36809 weblogic   nw-9000-06           JDBC Thin Client          
       367       4337 weblogic   nw-9000-06           JDBC Thin Client          
       368        681 weblogic   nw-9000-06           JDBC Thin Client          
       369      58808 weblogic   nw-9000-06           JDBC Thin Client          
       370         97 weblogic   nw-9000-06           JDBC Thin Client          

       SID         SN OSUSER     MACHINE              PROGRAM                   
---------- ---------- ---------- -------------------- --------------------      
       371      49579 weblogic   nw-9000-06           JDBC Thin Client          
       372      47717 weblogic   nw-9000-06           JDBC Thin Client          
       376      33487 weblogic   nw-9000-06           JDBC Thin Client          
       377         55 weblogic   nw-9000-06           JDBC Thin Client          
       378      45528 weblogic   nw-9000-06           JDBC Thin Client          
       380         58 weblogic   nw-9000-06           JDBC Thin Client          
       382      42176 weblogic   nw-9000-06           JDBC Thin Client          
       383       7448 weblogic   nw-9000-06           JDBC Thin Client          
       384      44990 weblogic   nw-9000-06           JDBC Thin Client          
       386      40172 weblogic   nw-9000-06           JDBC Thin Client          
       387      35807 weblogic   nw-9000-06           JDBC Thin Client          

       SID         SN OSUSER     MACHINE              PROGRAM                   
---------- ---------- ---------- -------------------- --------------------      
       388       3737 weblogic   nw-9000-06           JDBC Thin Client          
       389      63918 weblogic   nw-9000-06           JDBC Thin Client          
       391       7052 weblogic   nw-9000-06           JDBC Thin Client          
       399        909 weblogic   nw-9000-06           JDBC Thin Client          
       400         91 weblogic   nw-9000-06           JDBC Thin Client          
       401         61 weblogic   nw-9000-06           JDBC Thin Client          
       402         63 weblogic   nw-9000-06           JDBC Thin Client          
       403       2467 weblogic   nw-9000-06           JDBC Thin Client          
       404         53 weblogic   nw-9000-06           JDBC Thin Client          
       405         62 weblogic   nw-9000-06           JDBC Thin Client          
       406        127 weblogic   nw-9000-06           JDBC Thin Client          

       SID         SN OSUSER     MACHINE              PROGRAM                   
---------- ---------- ---------- -------------------- --------------------      
       407         67 weblogic   nw-9000-06           JDBC Thin Client          
       408      12333 weblogic   nw-9000-06           JDBC Thin Client          
       410      20060 weblogic   nw-9000-06           JDBC Thin Client          
       411         65 weblogic   nw-9000-06           JDBC Thin Client          
       412         63 weblogic   nw-9000-06           JDBC Thin Client          
       413         54 weblogic   nw-9000-06           JDBC Thin Client          
       414         58 weblogic   nw-9000-06           JDBC Thin Client          
       415         69 weblogic   nw-9000-06           JDBC Thin Client          
       416         56 weblogic   nw-9000-06           JDBC Thin Client          
       420         51 weblogic   nw-9000-06           JDBC Thin Client          
       421         55 weblogic   nw-9000-06           JDBC Thin Client          

       SID         SN OSUSER     MACHINE              PROGRAM                   
---------- ---------- ---------- -------------------- --------------------      
       422         55 weblogic   nw-9000-06           JDBC Thin Client          
       424         61 weblogic   nw-9000-06           JDBC Thin Client          
       426      39342 weblogic   nw-9000-06           JDBC Thin Client          
       428         46 weblogic   nw-9000-06           JDBC Thin Client          
       468      20839 weblogic   nw-9000-06           JDBC Thin Client          
       471      48166 weblogic   nw-9000-06           JDBC Thin Client          
       475      54276 weblogic   nw-9000-06           JDBC Thin Client          
       478      24234 weblogic   nw-9000-06           JDBC Thin Client          
       479       8998 weblogic   nw-9000-06           JDBC Thin Client          
       482      15969 weblogic   nw-9000-06           JDBC Thin Client          
       492      21510 weblogic   nw-9000-06           JDBC Thin Client          

       SID         SN OSUSER     MACHINE              PROGRAM                   
---------- ---------- ---------- -------------------- --------------------      
       493       2729 weblogic   nw-9000-06           JDBC Thin Client          
       495      17377 weblogic   nw-9000-06           JDBC Thin Client          
       496      19009 weblogic   nw-9000-06           JDBC Thin Client          
       497      31287 weblogic   nw-9000-06           JDBC Thin Client          
       499      39652 weblogic   nw-9000-06           JDBC Thin Client          
       502      44255 weblogic   nw-9000-06           JDBC Thin Client          
       503      47851 weblogic   nw-9000-06           JDBC Thin Client          
       514      44299 weblogic   nw-9000-06           JDBC Thin Client          
       519      23409 weblogic   nw-9000-06           JDBC Thin Client          
       521      36626 weblogic   nw-9000-06           JDBC Thin Client          
       526      39049 weblogic   nw-9000-06           JDBC Thin Client          

       SID         SN OSUSER     MACHINE              PROGRAM                   
---------- ---------- ---------- -------------------- --------------------      
       527      19197 weblogic   nw-9000-06           JDBC Thin Client          

100 rows selected.


（3）： 对于这种情况，可以先将中间件在操作系统中kill掉，也可以直接用这种方式在数据库中删除。删除后发现状态都为KILLED。此处由于100个session太多，单独kill太慢，于是通过pl/sql代码块进行批量kill。 


SQL> declare
  2  begin
  3  for i in (select sid,serial# sn from v$session where username='SIPO132')
  4  loop
  5  execute immediate 'alter system kill session '||chr(39)||i.sid||','||i.sn||chr(39);
  6  end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> select sid,serial# sn,osuser,machine,program
  2  from v$session
  3  where username='SIPO132'
  4  and status!='KILLED';

no rows selected


（4）：此时，删除后重建即可


SQL> drop user sipo132 cascade;

User dropped.

SQL> create user sipo132 identified by sipo default tablespace users temporary tablespace temp;

User created.

SQL> grant dba to sipo132;

Grant succeeded.

SQL> exit
（5）：在oracle用户中，impdp导入。此时会报出一个ora-31684：object type user:"sipo132" already exists的错误，这个可以忽略，因为我们在导入前已经建立了这个用户，且授予了权限。这么做为了方便起见。 

oracle@cpic-Dtest-01:~> impdp sipo132/sipo directory=data_pump_dir dumpfile=SIPO_20140606.DMP logfile=impdp-20140611.log remap_schema=SIPO:SIPO132 remap_tablespace=SIPO_T01:USERS


lmalds李麦迪
关注
专栏目录
中间件链接数据库报错求助
qq_40111796的博客
11-23 530
中间件链接数据库报错，日志信息如下，求助 com.icss.resourceone.sdk.connection.ConnectionLocatorException at com.icss.resourceone.sdk.connection.ConnectionLocator.getConnection(ConnectionLocator.java:127) at com.icss.resou...
PL/SQL Developer遇到一个奇葩无效标识符问题随记
深蓝@黄炎龙
11-18 6799
原创作品，出自 “深蓝的blog” 博客，欢迎转载，转载时请务必注明出处，否则追究版权法律责任。 深蓝的blog：前段时间前，在尝试使用PL/SQL Developer抽取数据时，执行脚本时，遇到了一个ora-00936报错。 查询报错ora-00936 ORA-00936: missing expression tips Cause: A required part of a clau
ORALCE无法删除用户，报错.ORA-01940:无法删除当前已链接的用户
bxcrbq2的专栏
12-19 124
在删除ORACLE用户的时候，报错如下图。这个时候，可以通过语句： select username,sid,serial# from v$session ; 查询所有当前已链接的用户。 这个时候，只需要kill需要删除的用户的sid,和serial即可，语句如下： alter system kill session'2309,2242'; 执行完以后，就可顺利drop掉想要删...
MySQL中间件的连接错误问题排查
杨建荣的学习笔记
06-25 432
这是学习笔记的第2020篇文章 最近在对一个线上的分布式环境做高可用配置，在流程测试通过后，发现中间件中出现了大量的连接错误。错误信息如下：INFO | jvm...
oracle报错ORA-01940: cannot drop a user that is currently connected解决方法
最新发布
红烧柯基的博客
06-06 1016
oracle报错ORA-01940: cannot drop a user that is currently connected解决方法
Oracle之ORA-01940：无法删除当前已连接用户的解决方案
qq_32103873的博客
12-20 2万+
由于资源占用，oracle报错01940，解决方案如下： 1.首先将索要删除的用户锁定，这句必须执行，否则之后杀死进程无效！ alter user 用户名 account lock; 2.从【v$Session】表查看当前用户占用资源，有使用资源的情况下，肯定不能删除用户 select saddr,sid,serial#,paddr,username,status fr...
2014-J2EE开发工作中遇到的异常问题及解决方法总结
热门推荐
rchm8519的专栏
11-30 2万+
本人做了近三年的J2EE开发工作，主要架构为SSH+MySQL+Tomcat，项目中也用到了一些第三方的开源工具或者中间件，工作中难免会遇到各种各样的异常问题，对于一些给我造成比较大的麻烦的问题，我习惯将他们和解决方案记录下来，以免下次遇到时再次东搜西找。这些问题有些可能并不是多难的技术问题，而只是容易被我们忽略而不易察觉的小问题，但是正因为不易察觉，它们往往会耗费我们比较多的时间，这点我相信很多
RAC下Fatal NI connect error 12170.报错处理
深蓝@黄炎龙
06-05 1万+
原创作品，出自 “深蓝的blog” 博客，欢迎转载，转载时请务必注明出处，否则有权追究版权法律责任。 深蓝的blog：   查看数据库告警日志，发现错误：Fatal NI connect error 12170报错   告警日志如下： *********************************************************************** Fat
RAC下Fatal NI connect error 12170 报错处理
qq_43685346的博客
01-13 334
RAC下Fatal NI connect error 12170 报错处理
数据库巡检有哪些
u012298337的专栏
10-22 1484
1. 检查数据库基本状况 在本节中主要对数据库的基本状况进行检查，其中包含：检查Oracle实例状态，检查Oracle服务进程，检查Oracle监听进程，共三个部分。 1.1. 检查Oracle实例状态 SQL> select instance_name,host_name,startup_time,status,database_status from v$instance; INST
ORA-01940: cannot drop a user that is currently connected
Hello World
03-20 778
删除数据库用户的时候经常会遇到这样的错误； ORA-01940: cannot drop a user that is currently connected 原因是有程序在连接我们需要删除的用户，我们删除用户之前一定要先把连接断掉； 1、查找帐号下哪些连接在运行： SELECT SID,SERIAL# FROM V$SESSION WHERE USERNAME='USERNAME'； ...
mysql的oracle删不了_oracle中用户删除不了，ORA-01940提示 “无法删除当前已连接用户”...
weixin_32184669的博客
01-19 259
Oracle删除用户的提示无法删除当前已连接用户两种解决方法如下：1、先锁定用户、然后查询进程号，最后删除对应的进程、在删除对应的用户SQL>alter user XXX account lock;SQL>SELECT * FROM V$SESSION WHERE USERNAME='LGDB'；SQL>alter system kill session 'xx,xx'SQL&g...
Oracle错误——ORA-01940: cannot drop a user that is currently connected
weixin_30325487的博客
06-10 429
背景 在oracle删除用户时，有的时候会提示ORA-01940: cannot drop a user that is currently connected，不能够删除用户。 原因 这是因为有其他的程序正在连接这个用户数据库，导致不能够删除用户 解决办法 前提：以dba角色操作：sqlplus / as sysdba。否则无操作权限 方法一：kill掉...
ORA-01940: cannot drop a user that is currently connected 的解决办法
Spring Boot-Common On With You
12-01 1万+
《 不能删除当前连接的用户的解决办法 》 关于 ： ORA-01940: cannot drop a user that is currently connected  的解决办法 这个异常是我以前项目开发过程中使用Oracle时遇到的一个问题，近期被几个小师妹经常问道起，我就将这个问题分享出来了，要是下次再问我直接就把链接发过去，自己看了。 问题的复现，如下图：
Oracle ORA-01940: 无法删除当前已连接的用户
wimpykids的博客
05-20 151
01.SQL> drop user a cascade; 02.drop user a cascade 03.* 04.ERROR 位于第 1 行: 05.ORA-01940: 无法删除当前已连接的用户 01.SQL> select username,sid,serial# from v$session; 02.USERNAME SID SERIAL# 03.------------------------------ -...
ORA-01940: cannot drop a user that is currently connected 问题解决
忙碌的菠萝
07-19 1604
想要重建用户及表空间，删除用户及表空间时报用户已连接。检查一波之后发现实际并没有在使用的，就只能杀除进程了。查找用户进程，并杀死进程。重新创建用户及表空间。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值