?
Solved

Kill session syntax

Posted on 2007-11-27
7
Medium Priority
?
2,296 Views
Last Modified: 2013-12-07
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;
0
Comment
Question by:xoxomos
  • 4
  • 3
7 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 1000 total points
ID: 20361680
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
 

Author Comment

by:xoxomos
ID: 20362023
Looks good to me!
I'll try it now.
Thanks
0
 

Author Comment

by:xoxomos
ID: 20366993
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 74

Expert Comment

by:sdstuber
ID: 20367096
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
 

Author Comment

by:xoxomos
ID: 20367724
Ok.  Just needs another immediate at the end :-)
      || ''''; IMMEDIATE
0
 

Author Comment

by:xoxomos
ID: 20367763
Better than ok.  It's outright sexy :-)
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 20367857
I try  :)
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question