Oracle 如何不停应用强行删除正在连接的用户

注意这个不是常规做法，建议还是停应用然后再删除用户。

一、 需求背景

业务方让导数据到测试环境，需要drop掉原用户，重新新建，然后导入。drop用户的时候如果有当前正在连接的会遇到经典报错 ORA-01940: cannot drop a user that is currently connected.，联系业务方停应用，但业务方说目前是微服务要停的话得停几十个应用，太麻烦，让直接kill会话。

我们知道如果直接kill肯定不行的，因为程序重试肯定比手动kill要快。另外这个测试环境还有别的业务在用其他用户连接，所以也不能停监听再kill。

后来尝试把密码改了，kill当前连接会话。连接的会话是kill完了（并且也没有会话在回滚），但是删除用户时还是报错ORA-01940，猜想可能与应用还在不断尝试连接有关。

 

二、 解决方法

后来尝试锁掉这个用户，再drop user，发现能成功。另外由于应用没有停，注意新建用户密码先不要跟原来一样，等把数据导入完了再改回原密码，否则应用会在导入的同时就过来写数据，很可能造成数据不一致。

整理操作步骤如下：

1. 锁定用户

alter user myuser account lock;

2. kill掉该用户所有当前会话

select 'alter system kill session '''||s.sid||','||s.SERIAL#||''' immediate;' from v$session s where s.status='INACTIVE' and s.USERNAME= upper('myuser');
-- 执行生成的kill语句
-- 验证当前已无该用户连接的会话
select sid,SERIAL#,status from v$session where USERNAME= upper('myuser');

3. 删除用户

drop user myuser cascade;

4. 修改数据库配置

这个应该是新建时就改好的，如果没改，在遇到大量使用错误密码登录时可能会有问题。

-- 设置default的profile的尝试登陆次数为无限
alter profile default limit failed_login_attempts unlimited;
-- 防止当应用使用错误密码高并发登录的时候，导致数据库大量library cache lock
-- 如果没设，新建用户后会出现大量library cache lock等待，不过业务本来就没在用，对导入影响不大
ALTER SYSTEM SET EVENT = '28401 TRACE NAME CONTEXT FOREVER, LEVEL 1' scope=spfile;

5. 用新密码创建用户并授权

create user myuser identified by "xxxx";
ALTER USER myuser QUOTA UNLIMITED ON myuser_data;
grant role_myuser,connect,resource to myuser;
alter user myuser default tablespace myuser_data;

6. 开始导入

impdp system@xxx DIRECTORY=DATA_PUMP schemas=myuser dumpfile=myuser.dmp logfile=myuser.log

7. 改回原密码

这个很重要，不然你导完了应用全连不上了。。。

alter user myuser identified by "原密码";

ok，可以让联系业务方检查系统情况了~