Oracle中删除用户和表空间的常见问题(比如:ORA-01940无法删除当前已连接用户的解决方案)-阿里云开发者社区

开发者社区> 数据库> 正文
登录阅读全文

Oracle中删除用户和表空间的常见问题(比如:ORA-01940无法删除当前已连接用户的解决方案)

简介: 这时候以管理员身份进入sqlplus命令窗口. 在删除用户的时候有时候会出现以下问题: ORA-01940无法删除当前已连接用户 这时候的解决方案是: 1)查看用户的连接状况   select username,sid,serial# from v$session (2)找到要删除用户的sid,和serial,并删除 例如:你要删除用户'WUZHQ',可以这

这时候以管理员身份进入sqlplus命令窗口.

在删除用户的时候有时候会出现以下问题:

ORA-01940无法删除当前已连接用户

这时候的解决方案是:
1)查看用户的连接状况
 
select username,sid,serial# from v$session
(2)找到要删除用户的sid,和serial,并删除
例如:你要删除用户'WUZHQ',可以这样做:
alter system kill session'532,4562';
这里的532表示的是sid,4562表示的是seria.
 
只有经过步骤(2)之后才可以删除用户
(3)删除用户
drop user username cascade
(**)如果在drop 后还提示ORA-01940:无法删除当前已链接的用户,说明还有连接的session,可以通过查看session的状态来确定该session是否被kill 了,用如下语句查看:
select saddr,sid,serial#,paddr,username,status from v$session where username is not null
结果如下(以我的库为例):
saddr                     sid           serial#            paddr              username              status
564A1E28              513           22974            569638F4        NETBNEW             ACTIVE
564A30DC              514          18183            569688CC        NETBNEW            INACTIVE
564A5644              516           21573            56963340        NETBNEW            INACTIVE
564B6ED0              531           9                   56962D8C        NETBNEW           INACTIVE
564B8184              532          4562               56A1075C www.2cto.comWUZHQ                KILLED
status 为要删除用户的session状态,如果还为inactive,说明没有被kill掉,如果状态为killed,说明已kill。
由此可见,WUZHQ这个用户的session已经被杀死。此时可以安全删除用户。
 
 删除表空间的步骤如下:
drop tablespace TS_MISPS including contents and datafiles cascade constraints;
这里的TS_MISPS是一个表空间的名字。
此外:
//including contents:表示删除表空间的内容,如果删除表空间之前表空间中有内容,而未加此参数,表空间删除不掉,所以习惯性的加此参数。
//including datafiles:表示删除表空间中的数据文件
//cascade constraints:同时删除tablespace中表中的外键参照
 
 
下面为您介绍的是Oracle用户会话信息的查询方法,该方法供您参考,如果您感兴趣的话,不妨一看。

通过V$SESSION视图,可以查询Oracle所有Oracle用户会话信息

select sid,logon_time,username,machine from v$session;

通过分组,统计每个不同的用户或主机打开的Oracle用户会话总数

select username,machine,count(*) from v$session group by username,machine;

根据SID和SERIAL#可以终止用户会话

ALTER SYSTEM KILL SESSION 'v_sid,v_serial#' immediate; (要真正的把v_sid和v_serial替换成数字)

Oracle用户会话的SID和SERIAL#可以通过V$SESSION视图查到:


  1. SQL> select sid,serial#,username, machine,status from v$session where username like 'PCNSH%';
  2. SID SERIAL# USERNAME MACHINE STATUS
  3. -------- ---------- --------------- ------------------------------- --------
  4. 366 14303 PCNSH197 client197.yourdomain.com INACTIVE
  5. 369 1745 PCNSH003 server009 INACTIVE
  6. 370 10165 PCNSH049 client049.yourdomain.com INACTIVE
  7. 371 18999 PCNSH056 client056 INACTIVE
  8. 372 6207 PCNSH056 client056 INACTIVE
  9. 373 7688 PCNSH197 client197.yourdomain.com INACTIVE
  10. 374 19246 PCNSH003 server009 INACTIVE
  11. 377 17649 PCNSH003 server009 INACTIVE

在Oracle中终止掉的会话只有在这个进程再次尝试连接Oracle时才会被pmon清除,在进程再次尝试连接之前,查询V$SESSION视图时还是可以看到该会话。

当用户会话无法响应时,上面的方法可能无法终止用户会话,只好直接杀死系统进程(谨慎)。

用户会话对应的系统进程可以通过V$SESSION和V$PROCESS两个视图来查询,通过会话的SID和SERIAL#可以查询到系统进程号。


  1. SQL> select p.spid from v$session s, v$process p
  2. where p.addr=s.paddr and s.sid=518 and s.serial#=41831;
  3. SPID
  4. ------------
  5. 16782

然后在操作系统中杀死进程:

kill -9 16782
某些软件会在启动时打开多个会话,当软件异常退出时源码天空,要批量的终止这些会话。存储过程基于用户名条件批量终止会话,创建存储过程脚本kill_user.sql如下:


  1. create or replace procedure kill_user(v_name in varchar2)
  2. as
  3. message varchar2(50);
  4. cursor loguser is select sid,serial# from v$session where username=upper(v_name);
  5. v_info loguser%rowtype;
  6. sql_mgr varchar2(1000);
  7. i number default 0;
  8. begin
  9. open loguser;
  10. loop
  11. fetch loguser into v_info;
  12. exit when loguser%notfound;
  13. i :=1;
  14. sql_mgr :='alter system kill session '''||v_info.sid||','||v_info.serial#||''' immediate';
  15. execute immediate sql_mgr;
  16. dbms_output.put_line('All the sessions of '||v_name||' have been killed');
  17. end loop;
  18. close loguser;
  19. if i=0 then
  20. raise_application_error(-20004,'The user '||v_name||' is not login the database currently!');
  21. end if;
  22. exception
  23. when no_data_found then
  24. raise_application_error(-20004,'The user '||v_name||' is not login the database currently!');
  25. end;
  26. /

调用上面存储过程,终止用户所有进程的方法如下:


  1. SQL> execute kill_user('user_name');

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章
最新文章
相关文章