删除oracle的某个用户时候,一般都无法删除,因为有session关联。
1.查看session:
select username,sid,serial# from v$session where username='SUNIMAGE';
select username,sid,serial# from v$session where username='IMAGING';
select username,sid,serial# from v$session where username='SUNFLOW';
结果:
2.批量拼接出,kill session 的语句:
declare
begin
for temp in(select username,sid,serial# from v$session
where username='IMAGING') --注意,这里最好用大写的用户名
loop
dbms_output.put_line('alter system kill session '''||to_char(temp.sid)||','||to_char(temp.serial#)||''''||';');
end loop;
end;
输出结果:
alter system kill session '104,21';
alter system kill session '125,28';
alter system kill session '174,15';
alter system kill session '214,176';
alter system kill session '242,76';
alter system kill session '334,49';
alter system kill session '362,44';
alter system kill session '393,6';
alter system kill session '474,61';
执行这些拼接出来的sql语句就好了。
3.其他:
其中,一般会涉及到转义字符
&的转义: ||'&'|| 或 ||chr(38)||
_的转义(下划线):'/_%' escape '/'
‘ 的转义(单引号):‘’