Re: Kill user sessions from PL/SQL

Hello Anatoly,

I guess , you've missed a call to dbms_sql.execute in your execsql-Procedure

-> dummy := DBMS_SQL.EXECUTE( c );

BTW a commit is not necessary if you only want to issue DDL-statements

Bye

Dirk

Anatoly Moskovsky schrieb in Nachricht ...
>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