Re: how to use execute immediate with session killing?

Hi !

If you are talking about DBMS_JOBS, then I regret to say that killing session of those job
is not possible in the normal way by KILL SESSION. The only ways to stop a job are

DBMS_JOB.REMOVE( )
(job owner user can do it)
or
set the job to 'broken' (even if it runs fine, but it will not be started in that case).
(job owner user can do it)
or
ALTER SYSTEM set job_queue_processes = 0 (that is: stop *all* jobs, only DBAs should be doing that.)

The reason for it is that the job session is not a normal user session by login, it is created by
an Oracle background process. The session "username" is USER, having no terminal in v$session,
and being permanently active.
Anf even if You succeded to kill it, it will be re-opened by Oracle withing job_queue_interval automatically.

hth

Jan.

"Ryan Gaffuri" <rgaffuri_at_cox.net> schrieb im Newsbeitrag news:1efdad5b.0302131021.3bef58bb_at_posting.google.com...
> this is dying on me. is there a way to do this dynamically?
>
> EXECUTE IMMEDIATE 'ALTER SYSTEM KILL SESSION '':1,:2''';
> USING v_sid, v_serial#;
>
> I have a requirement to allow users to be able to kill a job that is
> running. only way to do that is to kill the session. So Im giving them
> a wrapper.
Received on Thu Feb 13 2003 - 12:32:24 CST