Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
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