Re: Kill Session script not working

BirdRacer67_at_yahoo.com (BirdRacer) wrote in message news:<61d46e7c.0210181113.288e1143_at_posting.google.com>...
> I am attempting to write a kill session script that I can launch from
> a DOS-type batch file. Everything is working except one part.
>
> The script begins by doing a:
>
> select * from dba_blockers;
>
> Then:
>
> select sid,serial#,username,osuser,machine
> from v$session
> where sid IN (select holding_session from dba_blockers);
>
> That all works fine. Then I'm trying to do:
>
> alter system kill session 'xxx,xxxxx' <-- I am trying to have a query
> pass the results here such as:
>
> select sid,serial#
> from v$session
> where sid IN (select holding_session from dba_blockers);
>
> just like the above query. I can't figure out how to pass this as an
> argument to the "alter system kill session 'foo,blah'" command,
> however. Anybody know how to pass the sid and serial# results to that
> command? And they have to be in the correct syntax: '123,45678'
>
> This would more or less just show me the user holding the blocking
> lock, and then automatically kill it. I thought I might have it
> prompt me for the SERIAL#, or OSUSER, or something though, just to
> make sure I don't kill something inadvertently.
>
> If anybody can help I would greatly appreciate it. I've been pulling
> my hair out all day over this, and I didn't have much to start with.
> :)
>
> Thanks in advance

There are couple of ways.
1) Put this in a script and run it from batch file. e.g. KillSession.sql

CONNECT sys/password_at_host_string
DECLARE
   stmt_ VARCHAR2(100);
   sid_ NUMBER;
   serial_ NUMBER;
   CURSOR cur_ IS

      SELECT sid, serial# 
      FROM v$session 
      WHERE .....;

      sid_ := rec_.sid;
      serial_ := rec_.serial#;
      stmt_ := 'ALTER SYSTEM KILL SESSION ' || CHR(39) || sid_ || ',' ||  
                serial_ || CHR(39);
      EXECUTE IMMEDIATE stmt_;

From batch file
sqlplus _at_KillSession.sql

2) Create 'ALTER SYSTEM....' statements using sqlplus and spool them to a file e.g. SELECT 'ALTER SYSTEM...' from dual;
then run the file.

/Rauf Sarwar Received on Sun Oct 20 2002 - 03:42:59 CEST