cancel
Showing results for 
Search instead for 
Did you mean: 

Disconnecting users Part 2

SOLVED
Go to solution
Allen Karczmarek
Occasional Contributor

Disconnecting users Part 2

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.
3 REPLIES
Brian Crabtree
Honored Contributor
Solution

Re: Disconnecting users Part 2

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
Honored Contributor

Re: Disconnecting users Part 2

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
Honored Contributor

Re: Disconnecting users Part 2

Doh. 1 mistake.

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

Thats what I get for typing from memory.

Brian