Re: Kill Session question #2

ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 0; is the only way to *properly* prevent jobs to run.
In fact: this wil stop the snp0, snp1, ... processes. Active processes will stop when the job they are currently running is finished. Maybe this is the issue of your manager: setting job_queue_processes=0 is not the *only* thing needed to stop jobs *now*. After doing so running jobs must be killed.

You can kill the session, but you will see within the job_queue_interval period that the instance recreates the snp0, snp1,.... processes again. You will find in the alert.log that it is happening. And the error count of the job you killed will increase, and the job queue mechanism will retry the job, ignoring your interval using longer interval times after every failed run until it has tried it 16 times. Then the job gets broken.

Jan Gelbrich <j_gelbrich_at_westfalen-blatt.de> schreef in berichtnieuws b13fen$uas4e$1_at_ID-152732.news.dfncis.de...
| Hello,
|
| this time my question about killing sessions go to another direction than
my
| first thread.
|
| The story behind is this:
| My manager (who is our sysad btw ...)
| wants to _be able_ to kill the permanently active USER session that is
being
| created by entering
| ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 1;
|
| As most of You know, this is part of the setup for using DBMS_JOB.
|
| Now, a job can be _broken_ to make it not running. And I could make all
jobs
| broken,
| to make the job queue empty.
| But, of course, this would not effect the USER session in any way, it is
| still active.
| It is always there by showing 'rdbms ipc message'. So far, everything is
| normal.
|
| When I try to kill it like any other normal session by entering
| ALTER SYSTEM KILL SESSION '123,4576';
| , SQL*Plus freezes.
|
| When I use HORA to do so, it freezes.
| Some time before, my manager tried to do this (to make a shutdown, that
is),
| he got the message 'insufficient priviliges' ... that was some weeks ago.
| No clear recall of the conditions then.
|
| So after all:
| My question is:
| can this USER session, which is very special, be killed at all in the
usual
| way ?
|
| I am personnally used to set
| ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 0;
| in SQL*Plus when I want this special USER session to end,
| but my manager is insisting that there must be a way to kill it normally
| as he is used to do, using HORA (GUI tool, sending just
| the sesion killing statement to the server.)
|
| I thought that job queue sessions are special and so cannot be killed
| except altering the init parameter.
|
| Am I missing something, or is it really impossible ?
| (... yes I did RTFMing ...)
|
| I would be thankful about any tips or critics,
|
| tia
|
| Jan
|
| --Oracle EE 8.1.7 on AIX 4.3.3--
|
|
|
Received on Mon Jan 27 2003 - 11:06:16 CST