Dynamic sql script to kill over 500 sessions
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Dynamic sql script to kill over 500 sessions

  1. #1
    Join Date
    Dec 2001
    Location
    Baltimore, MD
    Posts
    372

    Dynamic sql script to kill over 500 sessions

    Hi
    Can anyone provide me with a dynamic sql script to kill more than 500 sessions
    Thanks
    Arsene Lupain
    The lie takes the elevator, the truth takes the staircase but ends up catching up with the lie.

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    Quote Originally Posted by Ablakios View Post
    Hi
    Can anyone provide me with a dynamic sql script to kill more than 500 sessions
    Thanks

    shutdown abort will do that for you

    anyway, the script depends on what you want to kill

    loop through v$session, pull out the sid and serial then kill it

  3. #3
    Join Date
    Nov 2002
    Location
    Mooresville, NC
    Posts
    349
    Modify this according to your requirement.

    declare
    V_STRING VARCHAR2(400);
    V_SID VARCHAR2(40);
    V_SERIAL VARCHAR2(40);
    cursor cur1 IS
    select SID,SERIAL# from v$session where serial#!=1 and status!='ACTIVE' and type !='BACKGROUND';
    begin
    For x in cur1 LOOP
    V_SID:=x.SID;
    V_SERIAL:=x.SERIAL#;
    V_STRING := 'ALTER SYSTEM KILL SESSION '''||V_SID||', '||V_SERIAL||'''';
    EXECUTE IMMEDIATE V_STRING;
    end loop;
    end;
    /
    http://www.perf-engg.com
    A performance engineering forum

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    ... and monitor rollbacks.

    Question, are you supposed to kill all sessions in the database? if Yes... you have to come up with a way to avoid new sessions get initiated most probably by stopping listeners and putting the database in restricted mode.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width