Disconnecting users Part 2

[Allen Karczmarek]

OK now the cusor is fine but I'm getting errors on the ALTER SYSTEM command below.
Again this is in a stored procedure.


CURSOR TO_BE_KILLED IS
SELECT username, sid, serial#
FROM v$session;

BEGIN

FOR connection IN TO_BE_KILLED LOOP
ALTER SYSTEM KILL SESSION connection.sid, connection.serial#;
END LOOP;

END;


PLS-00103: Encountered the symbol ALTER when expecting one of the following: BEGIN......


THANKS AGAIN.

[Brian Crabtree · Accepted solution]

Something like this:
------------------
create procedure KILLALL is
cursor listuser is select username,sid,serial# from v$session where username not like 'SYS%';
mysid varchar2(30);
inuser varchar2(30);
insid varchar2(10);
inserial varchar2(10);
sqlstatement varchar2(30);
begin
select distinct sid from v$mystat;
loop
fetch listuser into inuser,insid,inserial;
exit when listuser%NOTFOUND;
if insid != mysid then
sqlstatement:='alter system kill session '''||insid||', '||inserial||''';';
execute immediate sqlstatement;
end if;
end loop;
end;
/
--------------------

If you are going to use a "connection" variable, you will need to create a type to hold the data. This works fine, I just normally declare variables for them.

Brian

[Brian Crabtree]

Ohh yeah. Make sure you explicitly grant "alter system" to the user doing the kill. Same thing is true as with views.

Brian

[Brian Crabtree]

Doh. 1 mistake.

It should be:
select distinct sid into mysid from v$mystat;

Thats what I get for typing from memory.

Brian