OraFAQ Forum: Server Administration » Delete a running job

Home » RDBMS Server » Server Administration » Delete a running job

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Delete a running job [message #62105]

Fri, 25 June 2004 00:49

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

�

Report message to a moderator

Re: Delete a running job [message #62107 is a reply to message #62105]

Fri, 25 June 2004 02:45

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.

Report message to a moderator

Re: Delete a running job [message #62109 is a reply to message #62105]

Fri, 25 June 2004 03:37

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

are you the owner the job?

Report message to a moderator

Re: Delete a running job [message #62119 is a reply to message #62105]

Fri, 25 June 2004 16:31

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

Report message to a moderator

Re: Delete a running job [message #268637 is a reply to message #62119]

Wed, 19 September 2007 04:25

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

Report message to a moderator

Re: Delete a running job [message #268641 is a reply to message #268637]

Wed, 19 September 2007 04:39

Michel Cadot
Messages: 68686
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

Report message to a moderator

Re: Delete a running job [message #268652 is a reply to message #268637]

Wed, 19 September 2007 05:00

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

Report message to a moderator

Re: Delete a running job [message #268658 is a reply to message #268652]

Wed, 19 September 2007 05:04

Michel Cadot
Messages: 68686
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

kill -9

Regards
Michel

Report message to a moderator

Re: Delete a running job [message #268666 is a reply to message #268652]

Wed, 19 September 2007 05:37

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

Report message to a moderator

Re: Delete a running job [message #268676 is a reply to message #268666]

Wed, 19 September 2007 06:12

Michel Cadot
Messages: 68686
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

It is an OS command.

Regards
Michel

Report message to a moderator

Re: Delete a running job [message #268736 is a reply to message #268676]

Wed, 19 September 2007 09:11

DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland

Senior Member

ps -ef |grep Your process name

kill -9 processid

Report message to a moderator

Re: Delete a running job [message #268746 is a reply to message #268736]

Wed, 19 September 2007 10:21

Michel Cadot
Messages: 68686
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

Report message to a moderator

Re: Delete a running job [message #268841 is a reply to message #62105]

Wed, 19 September 2007 22:08

Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...

Senior Member

DBA_JOBS_RUNNING
Laughing

Laughing

Report message to a moderator

Re: Delete a running job [message #268917 is a reply to message #268841]

Thu, 20 September 2007 02:01

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

Report message to a moderator

Re: Delete a running job [message #269185 is a reply to message #268917]

Fri, 21 September 2007 00:45

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

Report message to a moderator

Re: Delete a running job [message #269267 is a reply to message #269185]

Fri, 21 September 2007 07:23

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

Embarassed

Thanks in advance.

Nayana

Report message to a moderator

Re: Delete a running job [message #269284 is a reply to message #269267]

Fri, 21 September 2007 08:24

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

nayana
Messages: 33
Registered: August 2007
Location: Bangalore

Member

Hi,

Still running! Razz

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

Report message to a moderator

Re: Delete a running job [message #269737 is a reply to message #269649]

Mon, 24 September 2007 06:31

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

Embarassed

Regards,
Nayana

Report message to a moderator

Re: Delete a running job [message #269791 is a reply to message #269737]

Mon, 24 September 2007 11:24

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

Report message to a moderator

Re: Delete a running job [message #270532 is a reply to message #269791]

Thu, 27 September 2007 06:33

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

Report message to a moderator

Re: Delete a running job [message #270557 is a reply to message #270532]

Thu, 27 September 2007 08:39

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);

Report message to a moderator

Re: Delete a running job [message #270736 is a reply to message #270557]

Thu, 27 September 2007 22:27

nayana
Messages: 33
Registered: August 2007
Location: Bangalore

Member

Hi,

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

Report message to a moderator

Re: Delete a running job [message #270738 is a reply to message #270736]

Thu, 27 September 2007 22:44

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

Report message to a moderator

Re: Delete a running job [message #270746 is a reply to message #270738]

Thu, 27 September 2007 23:13

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

Report message to a moderator

Re: Delete a running job [message #270759 is a reply to message #270746]

Thu, 27 September 2007 23:48

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

Razz

)

Help...pls...

Nayana

Report message to a moderator

Re: Delete a running job [message #270761 is a reply to message #62105]

Thu, 27 September 2007 23:56

BlackSwan
Messages: 26766
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.

Report message to a moderator

Re: Delete a running job [message #270782 is a reply to message #270761]

Fri, 28 September 2007 00:44

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

Report message to a moderator

Re: Delete a running job [message #270879 is a reply to message #270782]

Fri, 28 September 2007 04:20

nayana
Messages: 33
Registered: August 2007
Location: Bangalore

Member

Hey,

DBMS_job stopped!!! Laughing

Laughing

Used disable command.

dbms_scheduler.disable('job_name');

Thanks a Lot!!!!

Nayana

Report message to a moderator

Re: Delete a running job [message #271063 is a reply to message #270879]

Fri, 28 September 2007 21:42

mson77
Messages: 208
Registered: August 2007
Location: Brazil

Senior Member

Hello nayana,

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

mson77

Report message to a moderator

Re: Delete a running job [message #271127 is a reply to message #271063]

Sat, 29 September 2007 13:49

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

ora110
Messages: 42
Registered: September 2007
Location: China

Member

you should kill the process.
you should commit the action "delete job"

Report message to a moderator

Previous Topic:	ORA-01033 Oracle Initialization or shutdown in progress
Next Topic:	ORA-06521

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Sep 19 18:50:55 CDT 2024