Kill session syntax

Trying to get SQL or pl/sql syntax, maybe execute immediate.
I want to select sid, serial# from v$session  where program like '%TOAD%'; and pass that into
an execute immediate 'alter system kill session "'| | sid ||',' || serial ||"' immediate;
xoxomosAsked:
Who is Participating?
 
sdstuberCommented:
BEGIN
    FOR s IN (SELECT SID, serial#
                FROM v$session
               WHERE program LIKE '%TOAD%')
    LOOP
        EXECUTE IMMEDIATE 'alter system kill session ''' || TO_CHAR(s.SID) || ', ' || TO_CHAR(s.serial#)
                          || '''';
    END LOOP;
END;
0
 
xoxomosAuthor Commented:
Looks good to me!
I'll try it now.
Thanks
0
 
xoxomosAuthor Commented:
That's exactly the structure i wanted, but for some reason it does not do the execute immediate.
The pl/sql comes back executed successfully,, but the sessions remain.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
sdstuberCommented:
check the status of the sessions in v$session, it should say "KILLED",  it may take a while for smon to get around to actually remove them.  Plus, any transactions those sessions were in the middle of have to be rolled back.  

alter system kill session isn't a an immediate death, it's a signal to stop what you're doing and prepare to die.
0
 
xoxomosAuthor Commented:
Ok.  Just needs another immediate at the end :-)
      || ''''; IMMEDIATE
0
 
xoxomosAuthor Commented:
Better than ok.  It's outright sexy :-)
0
 
sdstuberCommented:
I try  :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.