Home » SQL & PL/SQL » SQL & PL/SQL » kill user sessions for certain user
kill user sessions for certain user [message #423501] Thu, 24 September 2009 19:32 Go to next message
casttree
Messages: 83
Registered: August 2008
Member
I can do the following steps to kill al sessions under certain user and hope to combine it into one query



select sid , serial# from gv$session where USERNAME = 'SCOTT' ;

57, 425
68, 230

and then
alter system kill session (57, 425);
alter system kill session (68, 230);




I tried to use WITH OF like

with sess as (
select sid , serial# from gv$session where USERNAME = 'SCOTT' ) alter system kill session (??(select sid from sess), (select serial# from sess)?? );

but no luck with it. Can we do it with one query?

Thanks,


[Updated on: Thu, 24 September 2009 19:36]

Report message to a moderator

Re: kill user sessions for certain user [message #423502 is a reply to message #423501] Thu, 24 September 2009 19:38 Go to previous messageGo to next message
BlackSwan
Messages: 24903
Registered: January 2009
Senior Member
> Can we do it with one query?
I can do it with a single SQL statement inside a LOOP.

You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed
Re: kill user sessions for certain user [message #423503 is a reply to message #423502] Thu, 24 September 2009 19:44 Go to previous messageGo to next message
casttree
Messages: 83
Registered: August 2008
Member
Yeah, I should not say in one query, one sql statement is good enough, could you give me more information for that case ?
Re: kill user sessions for certain user [message #423504 is a reply to message #423501] Thu, 24 September 2009 19:58 Go to previous messageGo to next message
BlackSwan
Messages: 24903
Registered: January 2009
Senior Member
EXECUTE IMMEDIATE SQL_STATEMENT

Re: kill user sessions for certain user [message #423505 is a reply to message #423504] Thu, 24 September 2009 20:10 Go to previous messageGo to next message
casttree
Messages: 83
Registered: August 2008
Member
then how can we put the sid and serial# into loop statement?
Re: kill user sessions for certain user [message #423506 is a reply to message #423505] Thu, 24 September 2009 20:18 Go to previous messageGo to next message
BlackSwan
Messages: 24903
Registered: January 2009
Senior Member
casttree wrote on Thu, 24 September 2009 18:10
then how can we put the sid and serial# into loop statement?



The solution depends upon which language, SQL or PL/SQL, is chosen.

You can compose SQL to generate the ALTER SYSTEM lines or write PL/SQL procedure which contains EXECUTE IMMEDIATE inside a LOOP.




Re: kill user sessions for certain user [message #423507 is a reply to message #423501] Thu, 24 September 2009 20:50 Go to previous messageGo to next message
casttree
Messages: 83
Registered: August 2008
Member
I can use

SELECT 'execute immediate alter system kill session ('|| '''' || sid || '''' || ',' || '''' || serial# || '''' || ');' FROM gv$session WHERE osuser = 'SCOTT'


to get

execute immediate alter system kill session ('135','3');
execute immediate alter system kill session ('137','6867');
execute immediate alter system kill session ('139','4400');
execute immediate alter system kill session ('141','1888');



But I am not sure how to put it in for loop in SQL and execute it

[Updated on: Thu, 24 September 2009 20:53]

Report message to a moderator

Re: kill user sessions for certain user [message #423508 is a reply to message #423507] Thu, 24 September 2009 20:59 Go to previous messageGo to next message
BlackSwan
Messages: 24903
Registered: January 2009
Senior Member
spool cleanup.sql
SELECT 'alter system kill session ('|| '''' || sid || '''' || ',' || '''' || serial# || '''' || ');' FROM gv$session WHERE osuser = 'SCOTT';
spool off
@cleanup.sql


Comment?

Re: kill user sessions for certain user [message #423516 is a reply to message #423501] Thu, 24 September 2009 23:47 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You cannot do it in a signle statement.
You can do it in a single PL/SQL block (which can be seen as a single statement).

Regards
Michel
Re: kill user sessions for certain user [message #423589 is a reply to message #423507] Fri, 25 September 2009 07:50 Go to previous message
joy_division
Messages: 4615
Registered: February 2005
Location: East Coast USA
Senior Member
casttree wrote on Thu, 24 September 2009 21:50

execute immediate alter system kill session ('137','6867');


You keep giving invalid syntax for an ALTER SYSTEM KILL SESSION command.

It's
alter system kill session '137,6867';
Previous Topic: Substr weirdness
Next Topic: update statement with :new.column throwing error
Goto Forum:
  


Current Time: Sun Sep 25 04:24:23 CDT 2016

Total time taken to generate the page: 0.09372 seconds