Home » RDBMS Server » Server Administration » Delete a running job
Delete a running job [message #62105] Fri, 25 June 2004 00:49 Go to next message
nadia azri
Messages: 8
Registered: February 2004
Junior Member
Dear all,

Got a problem with deleting a running job.

I've nearly tried everything:

   - Exec DBMS_JOB.REMOVE(JOB#) gives the error: ORA-23421 Job number xx is not a job in the job queue. I cannot understand because I see the job in tables DBA.JOBS and DBA.JOBS_RUNNING.

   - Tried also Exec DBMS_JOB.BROKEN(JOB#, true) without success.

   - Tried ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0, this command didn't give any error but the JOB still exists.

   - Tried to kill the thread through Orakill but I could not identify the process, didn't find any match when using:        Select s.sid, p.spid from v$session s, v$process p where s.paddr=p.addr and s.sid=<sid>; (sid identified from DBA.JOBS.RUNNING).

Note: I'm using Oracle8.1.7 and INTS.

Any help will be appreciated.

 Thanks.

Nadia azri

 
Re: Delete a running job [message #62107 is a reply to message #62105] Fri, 25 June 2004 02:45 Go to previous messageGo to next message
Phiphat
Messages: 3
Registered: January 2002
Junior Member
You try to remove from owner of JOB queue?
I got same error message when I try to remove queue from SYS.
Re: Delete a running job [message #62109 is a reply to message #62105] Fri, 25 June 2004 03:37 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
are you the owner the job?
Re: Delete a running job [message #62119 is a reply to message #62105] Fri, 25 June 2004 16:31 Go to previous messageGo to next message
Thomas Anderson
Messages: 9
Registered: January 2003
Junior Member
You can use:

dbms_job.broken(<id>,TRUE)

However this won't stop a job that's already running - it will only prevent it from starting. To stop a running job, corresponding session should be killed, which is not that trivial to do - you gotta locate the session in v$session and then alter system kill session '<v$session.sid> <v$session.serial#>' immediate (of course, <v$session.xxx> should be replaced with actual values for these
columns).

The most tricky part here is locating the session for the running job. Of course, job status must be also set to broken before killing its session, because Oracle will start new SNP process to replace killed one and this process will immediately pick up the job again since it was interrupted. And note that
if you set broken flag while job is running, and it then completes successfully, broken flag will be reset, so you really really need to kill running job before you set its broken flag.

Hope that helps,

clio_usa - 8/8i/9i OCP DBA

Oracle DBA Resources | Oracle DBA Forums & Usenet Newsgroups
Re: Delete a running job [message #268637 is a reply to message #62119] Wed, 19 September 2007 04:25 Go to previous messageGo to next message
nayana
Messages: 33
Registered: August 2007
Location: Bangalore
Member
Hi,

I want to delete a running job. So I used
 dbms_job.broken(job => 3962, Broken => TRUE);


and also used the following command.
 dbms_job.remove(3962);


But still job is running. Could you please tell me how to stop this.


Regards,
Nayana
Re: Delete a running job [message #268641 is a reply to message #268637] Wed, 19 September 2007 04:39 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't stop it unless you kill the process that is currently executing it.

Regards
Michel
Re: Delete a running job [message #268652 is a reply to message #268637] Wed, 19 September 2007 05:00 Go to previous messageGo to next message
nayana
Messages: 33
Registered: August 2007
Location: Bangalore
Member
Hi,

kill the process means? How?

Please let me know the code for this.


Regards,
Nayana
Re: Delete a running job [message #268658 is a reply to message #268652] Wed, 19 September 2007 05:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
kill -9

Regards
Michel
Re: Delete a running job [message #268666 is a reply to message #268652] Wed, 19 September 2007 05:37 Go to previous messageGo to next message
nayana
Messages: 33
Registered: August 2007
Location: Bangalore
Member
Hi,

kill -9 means? how to use this command?

When I used this command,

ORA-00900: invalid SQL statement



How to stop please advice.


Regards,
Nayana
Re: Delete a running job [message #268676 is a reply to message #268666] Wed, 19 September 2007 06:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is an OS command.

Regards
Michel

Re: Delete a running job [message #268736 is a reply to message #268676] Wed, 19 September 2007 09:11 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
ps -ef |grep Your process name

kill -9 processid
Re: Delete a running job [message #268746 is a reply to message #268736] Wed, 19 September 2007 10:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is an Oracle job, you can't get the process id like that.
dba_jos_running gives you the sid, there you get the process id.

Regards
Michel
Re: Delete a running job [message #268841 is a reply to message #62105] Wed, 19 September 2007 22:08 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

DBA_JOBS_RUNNING
Laughing
Re: Delete a running job [message #268917 is a reply to message #268841] Thu, 20 September 2007 02:01 Go to previous messageGo to next message
nayana
Messages: 33
Registered: August 2007
Location: Bangalore
Member
Hi,

Using HTMLDB application and tried the following commands:
select * from DBa_jobS_running;
No data found


ALTER SYSTEM KILL SESSION 'sid,serial#';



But still job is running. How to stop

Re: Delete a running job [message #269185 is a reply to message #268917] Fri, 21 September 2007 00:45 Go to previous messageGo to next message
mson77
Messages: 208
Registered: August 2007
Location: Brazil
Senior Member
Thomas Anderson wrote:
You can use:

dbms_job.broken(<id>,TRUE)

However this won't stop a job that's already running - it will only prevent it from starting. To stop a running job, corresponding session should be killed, which is not that trivial to do - you gotta locate the session in v$session and then alter system kill session '<v$session.sid> <v$session.serial#>' immediate (of course, <v$session.xxx> should be replaced with actual values for these
columns).

The most tricky part here is locating the session for the running job. Of course, job status must be also set to broken before killing its session, because Oracle will start new SNP process to replace killed one and this process will immediately pick up the job again since it was interrupted. And note that if you set broken flag while job is running, and it then completes successfully, broken flag will be reset, so you really really need to kill running job before you set its broken flag.




Please, after running these commands... tell us what solved this issue.

Thanks,


mson77
Re: Delete a running job [message #269267 is a reply to message #269185] Fri, 21 September 2007 07:23 Go to previous messageGo to next message
nayana
Messages: 33
Registered: August 2007
Location: Bangalore
Member
Hi,

SELECT SID, SERIAL#, STATUS
  FROM V$SESSION
  WHERE USERNAME = 'NAYANA';

answer is :
SID SERIAL# STATUS 
26   1884   ACTIVE 
29    851   ACTIVE 




ALTER SYSTEM KILL SESSION '23,1455';
Statement processed.


again when i tried above code to check the status then,

SELECT SID, SERIAL#, STATUS
  FROM V$SESSION
  WHERE USERNAME = 'NAYANA';

answer is:
SID SERIAL# STATUS 
23  1582    ACTIVE 
29   860    ACTIVE 




Please help me Embarassed

Thanks in advance.

Nayana
Re: Delete a running job [message #269284 is a reply to message #269267] Fri, 21 September 2007 08:24 Go to previous messageGo to next message
mson77
Messages: 208
Registered: August 2007
Location: Brazil
Senior Member
Hello nayana,

Issue:
ALTER SYSTEM KILL SESSION '23,1455' IMMEDIATE;


Regards,


mson77

[edited... complemented]=========================

Hello nayana,

Did you realize that after you issuing "alter system kill session.." command... when you run "select..." you get new sid/serial# ?

Did you pay attention on:
Thomas Anderson wrote:
Of course, job status must be also set to broken before killing its session, because Oracle will start new SNP process to replace killed one and this process will immediately pick up the job again since it was interrupted


Regards,


mson77

[Updated on: Fri, 21 September 2007 09:18]

Report message to a moderator

Re: Delete a running job [message #269649 is a reply to message #269284] Mon, 24 September 2007 00:14 Go to previous messageGo to next message
nayana
Messages: 33
Registered: August 2007
Location: Bangalore
Member
Hi,

Still running! Razz

I used dbms_job every 10 seconds. To stop that job used
 dbms_job.broken(jobno,TRUE);


Immediately used the following command.
dbms_job.remove(jobno);



After "alter system kill session.." command... when I run
SELECT SID, SERIAL#, STATUS
  FROM V$SESSION
  WHERE USERNAME = 'NAYANA';

I will get new sid/serial# .


Now am not able to identify the jobno because its removed. I tried kill & all commands as you mentioned but still job is running. Every every 10 seconds i will get a new mail. I want to stop this...how?

Regards,
Nayana
Re: Delete a running job [message #269737 is a reply to message #269649] Mon, 24 September 2007 06:31 Go to previous messageGo to next message
nayana
Messages: 33
Registered: August 2007
Location: Bangalore
Member
Hi,

1. Database ->Monitor ->session

SID Database Seconds_in_database  client_info
 22 ANONYMOUS                
 25 ANONYMOUS          3               NAYANA




If I selected SID no: 25 then I will get one tab "KILL SESSION"
But if selected that tab then I have got the following error.

 ORA-00027: cannot kill current session


Please help... Embarassed

Regards,
Nayana
Re: Delete a running job [message #269791 is a reply to message #269737] Mon, 24 September 2007 11:24 Go to previous messageGo to next message
vjeedigunta
Messages: 201
Registered: March 2005
Location: Hyderabad
Senior Member

select V$session.username, spid,v$session.sid, logon_time
from v$session,v$process
where v$session.paddr=v$process.addr and sid=25
order by logon_time desc;

get the spid from the above session ..and from the OS kill that process using kill -9 spid

-Sai Jeedigunta
Re: Delete a running job [message #270532 is a reply to message #269791] Thu, 27 September 2007 06:33 Go to previous messageGo to next message
nayana
Messages: 33
Registered: August 2007
Location: Bangalore
Member
Hi,

Thanks!

But my problem is every time when run the below command as you said i will get new SPID no.. How to kill the running job please help me out from this issue.


Regards,
Nayana
Re: Delete a running job [message #270557 is a reply to message #270532] Thu, 27 September 2007 08:39 Go to previous messageGo to next message
vjeedigunta
Messages: 201
Registered: March 2005
Location: Hyderabad
Senior Member

if you dont want the job why dont you remove it .. ?

exec dbms_ijob.remove(jobno);

Re: Delete a running job [message #270736 is a reply to message #270557] Thu, 27 September 2007 22:27 Go to previous messageGo to next message
nayana
Messages: 33
Registered: August 2007
Location: Bangalore
Member
Hi,

I removed job number using dbms_job.remove. But still job is running.

Re: Delete a running job [message #270738 is a reply to message #270736] Thu, 27 September 2007 22:44 Go to previous messageGo to next message
vjeedigunta
Messages: 201
Registered: March 2005
Location: Hyderabad
Senior Member

double check on what you are doing .. it should not run .. may be there are multiple jobs defined in the database ..
run the below selects and provide the output here ..

select * from dba_jobs_running;

select job, what , broken, failures, last_date, last_sec from dba_jobs;

-Sai
Re: Delete a running job [message #270746 is a reply to message #270738] Thu, 27 September 2007 23:13 Go to previous messageGo to next message
nayana
Messages: 33
Registered: August 2007
Location: Bangalore
Member
Hi,

select * from dba_jobs_running;
no data found 



select job, what , broken, failures, last_date, last_sec from dba_jobs;



and output for this is:
JOB BROKEN FAILURES LAST_DATE LAST_SEC WHAT
4001 N     0        19-SEP-07 08:55:08 wwv_flow_cache.purge_sessions(p_purge_sess_older_then_hrs => 24);
4002 N     0        19-SEP-07 16:47:17 wwv_flow_mail.push_queue(wwv_flow_platform.get_preference('SMTP_HOST_ADDRESS'
3982 Y     0        19-SEP-07 13:37:16 BDAY_PKG.BDAY_PROC;



Two jobs created one is for every 10 sec..and one for every midnight.
For every 10 sec.. job I used dbms_job.broken and dbms_job.remove. But still this job is running bcz every 10 sec..I will get a new mail.

Please help me ..
Re: Delete a running job [message #270759 is a reply to message #270746] Thu, 27 September 2007 23:48 Go to previous messageGo to next message
nayana
Messages: 33
Registered: August 2007
Location: Bangalore
Member
Hi,

To avoid the conflicts now I removed 3982 Job also.

But still job is running every 10 sec... (My mailbox is full.. Razz )


Help...pls...


Nayana
Re: Delete a running job [message #270761 is a reply to message #62105] Thu, 27 September 2007 23:56 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>select job, what , broken, failures, last_date, last_sec from dba_jobs;

SORRY, I don't believe above produced below

JOB BROKEN FAILURES LAST_DATE LAST_SEC WHAT
4001 N     0        19-SEP-07 08:55:08 wwv_flow_cache.purge_sessions(p_purge_sess_older_then_hrs => 24);
4002 N     0        19-SEP-07 16:47:17 wwv_flow_mail.push_queue(wwv_flow_platform.get_preference('SMTP_HOST_ADDRESS'
3982 Y     0        19-SEP-07 13:37:16 BDAY_PKG.BDAY_PROC;


The order of the columns do NOT match!

I am not sure the reality of anything by OP in this thread.
Re: Delete a running job [message #270782 is a reply to message #270761] Fri, 28 September 2007 00:44 Go to previous messageGo to next message
nayana
Messages: 33
Registered: August 2007
Location: Bangalore
Member
> select job, broken, failures, last_date, last_sec,what from dba_jobs;



"what" used at the end(bcz of its answer is very long).

Re: Delete a running job [message #270879 is a reply to message #270782] Fri, 28 September 2007 04:20 Go to previous messageGo to next message
nayana
Messages: 33
Registered: August 2007
Location: Bangalore
Member
Hey,

DBMS_job stopped!!! Laughing


Used disable command.
dbms_scheduler.disable('job_name'); 



Thanks a Lot!!!!


Nayana
Re: Delete a running job [message #271063 is a reply to message #270879] Fri, 28 September 2007 21:42 Go to previous messageGo to next message
mson77
Messages: 208
Registered: August 2007
Location: Brazil
Senior Member
Hello nayana,

Thank you for the feedback!
Appreciate a lot!
Regards,


mson77
icon1.gif  Re: Delete a running job [message #271127 is a reply to message #271063] Sat, 29 September 2007 13:49 Go to previous messageGo to next message
OracleDisected
Messages: 25
Registered: September 2007
Location: Mexico
Junior Member
Just for the record...

The operating system process for job queue is ora_jxxx ...where xxx is a consecutive number. When issued the scheduler disable it should go away.

Regards
[Edit MC: url to poster blog removed. We don't care about your blog in this post. If you have an answer on your site, post a link to this answer. If you don't, post your blog url in Marketplace]

[Updated on: Thu, 11 October 2007 12:42] by Moderator

Report message to a moderator

Re: Delete a running job [message #271223 is a reply to message #62105] Sun, 30 September 2007 22:59 Go to previous message
ora110
Messages: 42
Registered: September 2007
Location: China
Member

you should kill the process.
you should commit the action "delete job"
Previous Topic: ORA-01033 Oracle Initialization or shutdown in progress
Next Topic: ORA-06521
Goto Forum:
  


Current Time: Sat Dec 10 02:48:35 CST 2016

Total time taken to generate the page: 0.09257 seconds