在oracle用户正在连接时,试图用下面的存储过程先清除session,再删除该用户,但在执行 清除用户session 那条sql语句时,有时候会出现 “无效的sid或serial#”错误,请问下各位大神,有什么解决方法吗?create or replace procedure dropUser(user_name in varchar2) authid current_user is
cnt integer;
sqlStr varchar2(1000);
begin
sqlStr := 'alter user ' || user_name || ' account lock';
execute immediate sqlStr;
for x in (select * from v$session where username=user_name)
loop
--清除用户session
sqlStr := 'alter system disconnect session ''' || x.sid || ',' || x.serial# || ''' immediate';
execute immediate sqlStr;
end loop;
loop
select count(*) into cnt from v$session where username=user_name;
exit when cnt=0;
dbms_lock.sleep(3);
end loop;
sqlStr := 'drop user' || user_name || 'cascade';
end dropUser;
/
exec dropUser('user1');
cnt integer;
sqlStr varchar2(1000);
begin
sqlStr := 'alter user ' || user_name || ' account lock';
execute immediate sqlStr;
for x in (select * from v$session where username=user_name)
loop
--清除用户session
sqlStr := 'alter system disconnect session ''' || x.sid || ',' || x.serial# || ''' immediate';
execute immediate sqlStr;
end loop;
loop
select count(*) into cnt from v$session where username=user_name;
exit when cnt=0;
dbms_lock.sleep(3);
end loop;
sqlStr := 'drop user' || user_name || 'cascade';
end dropUser;
/
exec dropUser('user1');
解决方案 »
- 编写一个触发器
- For循环如何降序
- __Oracle 递归查询如何排序阿??????急!~~~~~~~~~~~
- ORACLE9i的表空间问题?
- 新手提问,请帮忙,非常谢!
- 这个sql该如何写
- oracle中jdbc怎样写?,是oracle.jdbc.OracleDriver?
- 各位大侠帮忙看我的oracle语句有什么问题,谢谢!急!等待ing.....
- bitmap index 的含义是啥?
- java.lang.ClassCastException: org.apache.xerces.parsers.XML11Configuration c
- 如何保证 insert 唯一?
- oracle dblink 如何删除视图
begin
execute immediate sqlStr;
exception when others then
null;
end;