?
Solved

Kill session syntax

Posted on 2007-11-27
7
Medium Priority
?
2,287 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 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Via a live example, show how to take different types of Oracle backups using RMAN.
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.
Suggested Courses

777 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