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

最新推荐文章于 2023-11-23 14:15:28 发布

edwzhang

最新推荐文章于 2023-11-23 14:15:28 发布

阅读量3.5k

点赞数

CC 4.0 BY-SA版权

分类专栏： ORACLE 文章标签： user system session kill sql 测试

本文链接：https://blog.youkuaiyun.com/edwzhang/article/details/7345721

ORACLE 专栏收录该内容

161 篇文章

订阅专栏

本文详细介绍了在Oracle数据库中删除用户时遇到的无法删除已连接用户的错误，并提供了通过查询会话ID和序列号来杀死相关会话，从而成功删除用户的解决方案。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

在本地的测试环境下，打算删除用户重建一下。

SQL> drop user XXXX cascade;
drop user pstk cascade
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected

SQL>

处理如下：

select sid,serial# from v$session where username = 'XXXX';

然后执行。

SQL> select sid,serial# from v$session where username = 'PSTK';

       SID    SERIAL#
---------- ----------
       846       3064
       847       3540
       848       1950
       850        173
       851       1627
       855       1724
       856       7025
       858       1284
       861       4561
       862       1317

10 rows selected.

SQL> select 'alter system kill session '''||sid||', '||serial#||''';' aa from v$session where username = 'PSTK';

AA
--------------------------------------------------------------------------------
alter system kill session '846, 3064';
alter system kill session '847, 3540';
alter system kill session '848, 1950';
alter system kill session '850, 173';
alter system kill session '855, 1724';
alter system kill session '856, 7025';
alter system kill session '858, 1284';
alter system kill session '861, 4561';
alter system kill session '862, 1317';

9 rows selected.

SQL>
SQL> alter system kill session '846, 3064';

System altered.

SQL> alter system kill session '847, 3540';

System altered.

SQL> alter system kill session '848, 1950';

System altered.

SQL> alter system kill session '850, 173';

System altered.

SQL> alter system kill session '855, 1724';

System altered.

SQL> alter system kill session '856, 7025';