kill sessions using loop 1

[helpdbas]

I'm trying to kill sessions on a very old Oracle server (8.0.5.0) with the following script:

begin
for x in (select SID,Serial#,status,last_call_et,username from v$session
where type='USER'
and status='INACTIVE'
and username = 'TESTER'
and last_call_et > 1200
) loop
execute 'Alter System Kill Session '''|| x.Sid
|| ',' || x.Serial# || '''';
end loop;
end;
/

I have a syntax or quote problem since the script errors at "'Alter" with an error stating encountered the symbol "Alter System Kill Session "' when expecting one of the following: :=.(@%; in Toad.

Any help would be appreciated for anyone who has this old of a version to test wtih.

 

[LKBrwnDBA]

Try:

    execute [b][red]immediate[/red][/b] 'Alter System Kill Session '''|| x.Sid
                     || ',' || x.Serial# || '''';

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[helpdbas]

Immediate doesn't work ... Immediate is highlighted with an error stating "encountered the symbol "IMMEDIATE" when expecting one of the following :=.(@%;

Any more suggestions?

 

[SantaMufasa]

Helpdbas,

Execute Immediate does work. You have something else wrong with your code. Here is a proof-of-concept that the code works:

select  SID,Serial#,status,last_call_et,username from v$session;

SID    SERIAL# STATUS   LAST_CALL_ET USERNAME
--- ---------- -------- ------------ --------
143        823 ACTIVE              0 DHUNT
147        856 INACTIVE            3 TESTER
150          1 ACTIVE          23806
152          1 ACTIVE          23810
154          1 ACTIVE          23830
159        352 ACTIVE             21
160          1 ACTIVE          23910
161          1 ACTIVE          23910
162          1 ACTIVE          23910
163          1 ACTIVE          23910
164          1 ACTIVE          23910

SID    SERIAL# STATUS   LAST_CALL_ET USERNAME
--- ---------- -------- ------------ --------
165          1 ACTIVE          23910
166          1 ACTIVE          23910
167          1 ACTIVE          23910
168          1 ACTIVE          23910
169          1 ACTIVE          23910
170          1 ACTIVE          23910

17 rows selected.

set serveroutput on format wrap
declare
  sql_stm varchar2(1000);
begin
 for x in (select  SID,Serial#,status,last_call_et,username from v$session
where type='USER'
and status='INACTIVE'
and username = 'TESTER'
             ) loop
        sql_stm := 'Alter System Kill Session '''|| x.Sid
                     || ',' || x.Serial# || '''';
        dbms_output.put_line (sql_stm);
        execute immediate sql_stm;
    end loop;
end;
/

Alter System Kill Session '147,856'

PL/SQL procedure successfully completed.

SID    SERIAL# STATUS   LAST_CALL_ET USERNAME
--- ---------- -------- ------------ --------
143        823 ACTIVE              0 DHUNT
147        856 KILLED            276 TESTER
150          1 ACTIVE          24079
...

If you wish to post your code and the associated error message (copied directly from your screen), perhaps we can help you resolve your challenge.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[SantaMufasa]

Ahhh...I just noticed that you are using Oracle 8.0.5. That version may have been too early to use "EXECUTE IMMEDIATE". I don't have that ancient a version available to test...sorry.

But, as you can see, if you upgrade to a version that wasn't de-supported many years ago, then the code works. <grin>

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[helpdbas]

Yep, Oracle 8.0.5 is old and we'd like to upgrade, but we aren't right at the moment.

Any more takers? What's wrong with my code? It looks like it would work if I had the proper quotes or something.

TYIA!

 

[SantaMufasa]

In the absence of "EXECUTE IMMEDIATE" from within PL/SQL, how about just generate the "ALTER SYSTEM..." command to a spooled script, "temp.sql", then issue a "SQL> @temp" at the SQL*Plus prompt?

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[helpdbas]

I'm not too familiar with all of the set commands to make it work this way, however I'm close to getting it working like you suggested. Can you help with how to get the output file to contain only my output from sql I run?



HERE IS WHAT I DO IN SQL PLUS:

set heading off

SPOOL e:\test.sql

select 'ALTER SYSTEM KILL SESSION '||sid||','||serial#||';'
from v$session
where type='USER'
and status='INACTIVE'
and username = 'TESTER';

SPOOL OFF



HERE IS WHAT I GET:

SQL> select 'ALTER SYSTEM KILL SESSION '||sid||','||serial#||';'
2 from v$session
3 where type='USER'
4 and status='INACTIVE'
5 and username = 'TESTER';

ALTER SYSTEM KILL SESSION 17,2442;

SQL> SPOOL OFF


I obviously don't want to see SQL> or my script, or SPOOL OFF in my output. I only want to see the output I generated from my script. Can you help?

 

[SantaMufasa]

Yes, Helpdbas, the following adjustments to your posted code should produce the results you seek:

set pagesize 0
set feedback off
set echo off
SPOOL test.sql

select 'ALTER SYSTEM KILL SESSION '||sid||','||serial#||';'
from v$session 
where type='USER'
and status='INACTIVE'
and username = 'TESTER';

SPOOL OFF

Explanations:
[tt]
set pagesize 0 : Suppress all column headings
set feedback off: Suppress words like "2 rows selected".
set echo off : Suppress the display of any commands from your script.
[/tt]
When I run the above version of your script, here are the results:

SQL> @tt_542
ALTER SYSTEM KILL SESSION 8,234;

The only contents of the file, "test.sql" are "ALTER SYSTEM KILL SESSION 8,234;".

Let us know if this resolves your need.



Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[SantaMufasa]

Actually, Helpdbas,

Your resulting script is missing the single quotes that should surround '8,234'. Add in the single quotes and you should be good to go.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[helpdbas]

Didn't work for me... I tried "set echo off" before the prior posting but it didn't seem to do anything and I'm not sure why? Could it be this version??

 

[SantaMufasa]

set echo off" works only when you invoke your code from a script. Did you invoke from a script, or did you copy and paste to the "SQL>" prompt?

If you did invoke from a script, could you please copy and paste your script contents and the results here in this thread so that we can continue to troubleshoot?

Thanks,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[helpdbas]

It works!!! Thank you for teaching me about these set commands.

This will be a big help for our company. Thanks for helping me look good.

 

[Beilstwh]

Helpdbs,
We have currency on this forum to show your appreciation. on any of Santa's postings, you will see a tag "thank SantaMufasa for this valuable post" Click on it and he gets a red star which doesn't cost you anything but is used to show the most appreciated member in the forums.

Bill
Lead Application Developer
New York State, USA