Closing all connections 3

[ddrillich]

Good Day,

Yesterday, our application didn't manage to close all its connections to the DB. We had to bounce the instance which caused the backup to stop - not pleasant.

Is there any other way to close all the connections?

Regards,
Dan

 

[engineer2100]

Got some time before I leave....

Why not kill the sessions after vieweing V$Session?

 

[ddrillich]

Right, how do I kill them?

 

[engineer2100]

Alter System Kill Session 'Sid,Serial#';

SID and Serial# are from V$SESSION.

HTH

-Engi

 

[ddrillich]

Engi,

Sounds great - thank you!

Regards,
Dan

 

[ddrillich]

http://www.oracle-base.com/articles/misc/KillingOracleSessions.php

seems to cover the subject well.

 

[ddrillich]

Good Day,

I'm trying to generate the SQL dynamically and so far I came up with the following SQL -

select 'Alter System Kill Session ' || "'" || Sid || "," || Serial# || "'" || " IMMEDIATE;"  from (select Sid, Serial#,program from v$session where program='Intraspect5EE.exe' or program='kcs.exe');

It seems that I need to escape somehow the quote. Any idea how to do it?

Regards,
Dan

 

[SantaMufasa]

Dan,

You have hit one of my biggest bugga-boos I've had with Oracle since I first used it in 1988: Oracle's infamous single-quote-only recognition.

To make your code work, here is the modification:

select 'Alter System Kill Session '''|| Sid || ',' || Serial# || ''' IMMEDIATE;"  from (select Sid, Serial#,program from v$session where program='Intraspect5EE.exe' or program='kcs.exe');

Notice that to leave a single quote in your output string, you must place two successive single quotes, and that's in addition to the beginning and ending single quotes. We use double quotes in Oracle only to surround column aliases.

Let us know how this change works for you.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[SantaMufasa]

Oops..."Warning Will Robinson, Warning!"

I forgot to end your code string with a single quote, replacing the erroneous double quote.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[dbtoo2001]

select 'Alter System Kill Session ' || ''' || Sid || ',' || Serial# || ''' || ' IMMEDIATE;' from (select Sid, Serial#,program from v$session
where program='Intraspect5EE.exe' or program='kcs.exe')
/

 

[dbtoo2001]

Dang, Santa's too quick for me... must be how he gets to all of those Oracle DBA's houses on christmas eve.

 

[SantaMufasa]

HoHoHo, DBToo. You're always such a good DBA...you'll get what's on your wish list, as always.

BTW, in your code, above, in each of the two places where three single quotes appear ('''), you'll need four ('''') to avoid Oracle disrespecting your code.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[ddrillich]

Thanks a lot Dave and DBToo!!!

 

[ddrillich]

Good Day,

The SQL query ran successfully. Thank you!

SQL> select 'Alter System Kill Session '''|| Sid || ',' || Serial# || ''' IMMEDIATE;' from (select Sid,Serial#, program from v$session where program='Intraspect5EE.exe' or program='kcs.exe');

'ALTERSYSTEMKILLSESSION'''||SID||','||SERIAL#||'''IMMEDIATE;'
--------------------------------------------------------------------------------

Alter System Kill Session '22,817' IMMEDIATE;
Alter System Kill Session '23,34158' IMMEDIATE;
Alter System Kill Session '24,56625' IMMEDIATE;
Alter System Kill Session '28,10124' IMMEDIATE;
Alter System Kill Session '54,50122' IMMEDIATE;
Alter System Kill Session '55,43584' IMMEDIATE;
Alter System Kill Session '56,12077' IMMEDIATE;
Alter System Kill Session '62,1104' IMMEDIATE;
Alter System Kill Session '63,1761' IMMEDIATE;
Alter System Kill Session '66,25351' IMMEDIATE;
Alter System Kill Session '72,51293' IMMEDIATE;

11 rows selected.


Now, it would be nice to execute these eleven queries in one command. I guess I can spool it... and then run it.

Any ideas?

Regards,
Dan

 

[SantaMufasa]

Dan,

Actually, to save spooling, you could just run all of this "kills" as a single PL/SQL "EXECUTE IMMEDIATE..." command inside of an implicit CURSOR FOR LOOP.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[ddrillich]

Dave,

Sounds great - can you please bestow upon me the details ;-) Never wrote in PL/SQL... ?

Regards,
Dan

 

[SantaMufasa]

Dan,

Following is code that works on any Oracle versions from 8.1.x onward:

begin
    for x in (select  SID
                     ,Serial#
                     ,program
                     ,username
                from v$session
                       where program='Intraspect5EE.exe'
                          or program='kcs.exe'
             ) loop
        execute immediate 'Alter System Kill Session '''|| x.Sid
                     || ',' || x.Serial# || ''' IMMEDIATE';
    end loop;
end;
/

I simplified your original SELECT and placed it into a PL/SQL block. Just copy and paste the code, above, at your SQL> prompt, or save it to a script to run with an "@<script>" invocation.

Let us know your thoughts.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[ddrillich]

Dave, beautiful thing!! Worked very well and closed the 11 connections that refused to go down.

 

[ddrillich]

I guess one can give only one star. Trying to submit another vote didn't go through.

 

[SantaMufasa]

Dan,

Not a problem...It's your thought that counts.

s are fun, but not vital (although my kids presented me last year with a t-shirt that reads, "Will Work for

s" <grin>

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]