Solved

Kill session syntax

Posted on 2007-11-27
7
2,279 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
Independent Software Vendors: 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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

Suggested Solutions

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ā€¦
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
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 video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

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