Re: Kill user sessions from PL/SQL

Thanks. It worked.
But why "parse" is enough to execute a DDL statement like "create function .." and isn't enough to execute "alter system" ? How can I determine for a specific command whether i need to call "execute" method or not?

"Dirk Köser" <D.Koeser_at_DeutschePost.de> wrote in message news:8h2ifu$na2_at_ppd00021.deutschepost.de...
> 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