Re: dbms_job query?

Hi Seema,

• v$session.status="KILLED" is not enough to say that session is really killed. Check v$session.server column, too. If it's something other than pseudo i.e. dedicated,shared, it means that there are activities such as network I/O or rolling back a transaction, etc.
• As I remember If an snp is killed, it wakes up after a while(but, I'm not sure)
• If PMON has a lot of activities, it may take time to clean all resources allocated by killed session.
• PMON can not remove session object. It is removed when a killed session tries to make kernel call (gets ora-28).
• If a transaction is killed, current working statement is not killed immediately. for example:
  • insert
  • select ...
  • update 1000<------ KILL here
  • update 5

If you kill during updating 1000 rows, Oracle will update 1000 rows, then session will be killed before updating 5 rows. what about SELECT (???)

--

Web: http://www.unal-bilisim.com
*********
Questions and Answers: http://www.unal-bilisim.com/qa/discus/
*********
itrprof SQL Analyzer: http://www.unal-bilisim.com/products/itrprof/itrprof.html

itrprof SQL Analyzer is a web based tool which analyzes
SQL_TRACE/Event10046 trace files of Oracle,
finds bottlenecks and offersinformation on how to tune them.
*********
iOraBugFinder:
http://www.unal-bilisim.com/products/iorabugfinder/iorabugfinder.html

iOraBugFinder is a web based tool which scans Oracle's alert log files,
ORA-600 and ORA-7445 trace files; extracts relevant information from them
and generates URL links to relevant bugs, notes, forums at Metalink.
*********


ARUN K C wrote:



> we had these situations  for this we had to break the job first and then

> kill the snp process after some time u will see the sid out of v$session.

> Even if you kill the snp it will be kicked back later on from pid or spid i

> think i am not sure but it works

> Try this out it should work

> Well there must be some better answers from the experts out here check it

> out

>

> >From: "Seema Singh" <oracledbam_at_hotmail.com>

> >Reply-To: ORACLE-L_at_fatcity.com

> >To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>

> >Subject: dbms_job query?

> >Date: Fri, 01 Jun 2001 14:11:10 -0800

> >

> >Hi

> >If status=KILLED  in v$session and the process is SNP oracle background

> >process corresponding to that sid and dba_jobs_running has the same sid,no

> >entry corresponding in dba_jobs then how to kill that process.

> >Thx

> >-seema

> >_________________________________________________________________

> >Get your FREE download of MSN Explorer at http://explorer.msn.com

> >

> >--

> >Please see the official ORACLE-L FAQ: http://www.orafaq.com

> >--

> >Author: Seema Singh

> >  INET: oracledbam_at_hotmail.com

> >

> >Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

> >San Diego, California        -- Public Internet access / Mailing Lists

> >--------------------------------------------------------------------

> >To REMOVE yourself from this mailing list, send an E-Mail message

> >to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in

> >the message BODY, include a line containing: UNSUB ORACLE-L

> >(or the name of mailing list you want to be removed from).  You may

> >also send the HELP command for other information (like subscribing).

>

> _________________________________________________________________________

> Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.

>

> --

> Please see the official ORACLE-L FAQ: http://www.orafaq.com

> --

> Author: ARUN K C

>   INET: arun_k_c_at_hotmail.com

>

> Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051

> San Diego, California        -- Public Internet access / Mailing Lists

> --------------------------------------------------------------------

> To REMOVE yourself from this mailing list, send an E-Mail message

> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in

> the message BODY, include a line containing: UNSUB ORACLE-L

> (or the name of mailing list you want to be removed from).  You may

> also send the HELP command for other information (like subscribing).





-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Danisment Gazi Unal (Unal Bilisim)
  INET: danisment.unal_at_unal-bilisim.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).