Writing a query to kill a user session

Question

Hi,

I have to write a query which should do the following task

select SID from v$session where username = 'some user'

and if there is any SID by that particular username then kill that SID using the command below :

alter system kill session 'sid';

What I have wrtten currently is the following :

alter system kill session
    where sid = select sid from v$session where username = 'some user'

This query fails when there is no particular sid by that username

The query has to be such that there is no usage of curly braces '{' or '}'

And the query should be just a single line query, no multiple line queries.

The DB is ORACLE10g

Any suggestions??

Answer 1

What I usually do is:

select 'alter system kill session '''||sid||','||serial#||''';'
from v$session
where username = 'someuser';

This will return results like:

alter system kill session '11,222';
alter system kill session '22,444';

I then just copy and paste the result I want to execute.

No curly braces (how could there be any?), but I'm not sure it meets your one-line rule.