Solved

Kill session syntax

Posted on 2007-11-27
7
2,283 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 250 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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

696 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