Re: Kill Session question #2

Just an extra bit of info for your manager:

SNPn processes are actually part of replication subsystem (SNP stands for SNaPshot) and are used to deliver table snapshots in background to destination systems. Coincidentally, they appeared to be useful for scheduling user jobs as well, hence DBMS_JOB was made available to public. Certainly, you don't want to mess with replication and kill these processes manually - that's why PMON restarts them if they die for any reason and that's why they are so pervasive in trying to finish their job queue. For these reasons, your manager should consider these processes as background Oracle processes (which they in fact are) and do not attempt to kill them using regular means. SNPn processes indeed do impersonate various users during their job execution runs, but in no way they are user processes.

References:
 Oracle8i Concepts, Part III, Process Architecture, Background Processes  Oracle8i Administrator's Guide

--
Vladimir Zakharychev (bob@dpsp-yes.com)                http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.


"Jan Gelbrich" <j_gelbrich_at_westfalen-blatt.de> wrote in message
news:b15d4v$v0hbv$1_at_ID-152732.news.dfncis.de...


> Thank You, Anton, for this confirmation.

>

> Maybe I can explain things better now to my manager, why he _cannot_ do what

> he wants in this case.

> And it makes sense. A session that is opened by Oracle itself just after

> job_queue_processes is altered

> is different from normally user opened sessions at a terminal. It would be

> like killing

> the PMON or other background sessions - totally strange idea.

>

> I think this thought only comes up, when you are used to use a GUI-Tool like

> HORA

> (which I use as well, supplementary to my scripts, to supervise all sessions

> with one button),

> only foreground sessions can be seen (derived from v$session),

> and the job queue session is something between,

> showing an event 'rdbms ipc message', and being permanently active. This

> prevents to shutdown the db

> normally. And when my manager wanted to shutdown once (some weeks ago, and I

> was absent),

> he was not able to do that because of the active and un-killable job queue

> session.

> And so he finally did ... shutdown abort |-(, fortunately no problems arose

> from that afterwards ...

>

> O.K. I have learned one more piece of Oracle.

>

> ThanX

>

> Jan=)

>

> "Anton Buijs" <aammbuijs_at_xs4all.nl> schrieb im Newsbeitrag

> news:3e35670c$0$49100$e4fe514c_at_news.xs4all.nl...

> > 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.

> >

> > | ... So after all:

> > | My question is:

> > | can this USER session, which is very special, be killed at all in the

> > usual

> > | way ?

>

>

>