今天在进行测试环境导数据的时候, 需要记性 drop user 操作在执行 drop user 的时候, 提示: ORA-01940: cannot drop a user that is currently connected
- SQL> drop user ecity ;
- drop user ecity
- *
- ERROR at line 1:
- ORA-01940: cannot drop a user that is currently connected
问题很明显, 有用户在连接, 不允许 drop 掉该 user
- select username,sid,serial#,paddr from v$session where username='ECITY';
- USERNAME SID SERIAL# PADDR
- ------------------------------ ---------- -------------------------------------------------
- ECITY 634 7 00000000C028D198
- SQL> select PROGRAM from v$process where addr='00000000C028D198';
- PROGRAM
- ----------------------------------------------------------------------------------------------------------
- Oracle@oradb01 (DW00)
- SQL>
- SQL> alter system kill session '634,7';
- System altered.
- SQL>
- SQL> select saddr,sid,serial#,paddr,username,status from v$session where username is not null;
- SQL> drop user ecity CASCADE;
- User dropped.
- SQL>
问题解决, 记得 KILL 进程前, 先看看是啥进程, 哪台机连过来的, 能否 KILL 等等测试环境还好, 生产无小事
来源: http://www.linuxidc.com/Linux/2018-03/151511.htm