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

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

 

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';

System altered.

SQL> alter system kill session '858, 1284';

System altered.

SQL> alter system kill session '861, 4561';

System altered.

SQL> alter system kill session '862, 1317';

System altered.

SQL> drop user XXXX cascade;

User dropped.

SQL>