Kill user sessions from PL/SQL

Hi!
I need to kill all user sessions from a stored proc.(I'm implementing an advanced version of DROP USER command that would be able to drop a user irrespective of existing user sessions )

Below it's shown what I do (but this does not work):

Create user Killer ....;
Grant DBA to Killer;
Grant Alter system to Killer;
Grant select any table to Killer;

connect Killer...;
-- a helper proc

Create Or Replace procedure
ExecSQL(s_sql in long)
as
c integer;
begin
  c := dbms_sql.open_cursor;
  Begin
    dbms_sql.parse(c, s_sql, dbms_sql.NATIVE );   Exception When Others Then
   dbms_sql.close_cursor(c);
    raise;
  End;
  dbms_sql.close_cursor(c);
end;
/

• my proc CREATE OR REPLACE procedure pr_killusersessions(as_user in varchar2) as ls_sql long; Begin For rec In (select sid,serial#,osuser,program from v$session Where Upper(as_user) in (username, schemaname)) Loop ls_sql:= 'alter system kill session '''||rec.sid||','||rec.serial#||''''; dbms_output.put_line(ls_sql); ExecSql(ls_sql); End Loop; Commit; End; /
• then we're trying to kill all sessions of user TEST SVRMGR> set serveroutput on Begin pr_KillUserSessions('test'); End; / Statement processed. alter system kill session '11,6974' SVRMGR>
• there's no errors but the session wasn't killed
• but if I just execute from console: SVRMGR>alter system kill session '11,6974';
• it will work What did I miss of? Probably, the proc. owner does not have enough system rights. But which? Thanks.

Bye Received on Wed May 31 2000 - 00:00:00 CDT