6 Replies Latest reply: Jun 14, 2012 3:34 PM by BSalesRashid RSS

    Can't stop a job execution

      I have Oracle 10g and a scheduled job which executes a hot backup every day at 2 AM.
      Yesterday evening at 10 PM the listener hung. I rebooted the machine this morning and everything is fine now.

      However, the Jobs page shows, that the last backup has been running for 15 hours now!

      When I try to stop it, it says "The job was stopped successfully", but I see status is still "Running". I tried to delete it, but it says it must be stopped first.

      Is there any way to kill this job?

      I know there is a DBMS_SCHEDULER.STOP_JOB procedure, but it requires job name and I don't know how to get that.
      I didn't find anything useful in dba_jobs or any other view.
        • 1. Re: Can't stop a job execution
          Warren Tolentino
          this link might be of help DBMS_SCHEDULER
          • 2. Re: Can't stop a job execution
            Are you sure this is a Scheduler job (dbms_scheduler package). Scheduler jobs are stored in the ALL_SCHEDULER_JOBS view.

            select owner, job_name , state from all_scheduler_jobs ;

            Every scheduler job is identified by its name (as well as its owner) which is shown in all views and on all applicable EM web pages.

            Scheduler jobs should never be stuck in the running state. Any terminated job is normally cleaned up after a minute or 2 (or when the database is started up).
            • 3. Re: Can't stop a job execution
              I've had similar problems from time to time and have found nothing other than shutting down em and the database to clear them. Of course, for most of us that's not always a viable option. Just wondering if anyone has had any luck getting rid of jobs like this without shutting down the DB. As for the question of whether or not it's a scheduler job, it is not. It's actually handled through EM, so I've got no idea where these are actually stored. I've had the issue with both 10g rel 1 and rel 2.

              Thanks for any help,
              • 4. Re: Can't stop a job execution
                I have had some success with finding the offending session and killing it, then killing the offending OS process.
                Since we are using RAC (on RHEL), and the session is known to be running under a job class, which runs under a service, we find the offending session by:

                select sid, serial#, status from gv$session where service_name = 'BACKUPS';

                SID SERIAL# STATUS
                ---------- ---------- --------
                3185 4664 ACTIVE

                Then issue: ALTER SYSTEM KILL SESSION '3185, 4664' IMMEDIATE;

                SID SERIAL# STATUS
                ---------- ---------- --------
                3185 4664 KILLED

                Now, find the OS process associated with that SID (assuming you are using UNIX):

                select s.username, s.status, s.sid oracle_sid, s.process unix_pid, s.serial#, p.spid, s.machine, s.lockwait
                     from v$session s, v$process p
                     where s.sid = '&oracle_sid'
                     and s.paddr = p.addr
                Enter value for oracle_sid: 3185

                ------------------------------ -------- ---------- ---------
                APP_SERVER INACTIVE 3185 1234

                Then, leave sqlplus, and kill the offending OS process using a command prompt on the server:

                kill -9 1234

                After this, the job can be disabled/modified/dropped
                • 5. Re: Can't stop a job execution
                  Just a quick clarification. This method should only be necessary for dbms_job jobs.

                  For jobs created with dbms_scheduler using dbms_scheduler.stop_job with force=>true does exactly this. Using force=>false does a more graceful stop (similar to sending ctrl-c to sqlplus).

                  • 6. Re: Can't stop a job execution
                    Got the same problem, and had the same ideia: Killing the process at OS.
                    But, as soon as i kill the job, another OS Process assume the job.

                    Any way to kill this job and disable it without shutting down the database ?