Re: Kill user sessions from PL/SQL

From: Michael M <~okana_at_voidnet_no_spam.net>
Date: 2000/07/03
Message-ID: <el385.23413$kh4.42979@east2.usenetserver.com>#1/1

Anatoly -

First off - thank you for your question and code - it gave me the direction I needed for something similar - and I wonder if my solution, a little more global in scope, would be of assistance to you or anyone else out there.

For some backup and maintenance procedures I needed to kill all user sessions except for the SYS and system connections.

Below is the EXEC stored procedure and the modified SQL to kill all users within an instance:

**ExecSQL Stored procedure

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

 **SQL that is saved and run from a command file or can become a stored procedure:

DECLARE
ls_sql long;
Begin
For rec In (select sid, serial# from v$session

            Where username is not null and username <> 'SYS') Loop

        ls_sql:= 'alter system kill session '''||rec.sid||','||rec.serial#||'''';

        dbms_output.put_line(ls_sql);
        ExecSql(ls_sql);

End Loop;
End;

Mike M
mailto:~okana_at_voidnet_no_spam.com
 (to reply, remove the ~ and "_no_spam")

"Anatoly Moskovsky" <avm_at_trais.com.ua> wrote in message news:HVN2591C576_at_heaven.org...
> 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 Mon Jul 03 2000 - 00:00:00 CDT