How do I killing a running Job

[oisaya]

I have a distributed network that fails occassionally. When it fails, the running job transaction session cannot be killed. DBMS_job.remove or .broken does not help. Does any one have a script that can unlock any job queue lock or terminate the session without doing shutdown abort? Trying to kill the session doesn't produce the desired effect.
This is an AIX box and 8.1.6 product.

Oisaya

[puneet]

get the SID , serial 3 from v#session where sid in V$LOCK .
Then kill the session in SQLPLUS by alter database Kill session (sid, serial#)

or get the SPID from v$process table and kill at OS LEVEL by Kill -9 SPID

regards

[steeve123]

It's alter system kill session 'sid, serial#';


Steeve Bisson
EMail: steeve_2@videotron.ca

[oisaya]

I have tried to kill the session with no success. It shows that the session has been killed, yet the database still hangs and I have to do shutdown abort and restart the instance before any work can be done on this machine.

I have not tried killing it at the OS level yet.

Oisaya

[puneet]

Try OS level killing next time

use Kill -9 PID for that

[marist89]

You will have to wait for the transaction to rollback after you issue the alter system kill session.

[marist89]

I try to stay away from kill -9 because it always doesn't kill the process on the backend.

[Prof]

select * from dba_jobs
and check next_date
perhaps
1. update this table ( next_date ).
2. change system date on system.

[Prof]

And
shutdown abort
-- change time
startup
of course