6

My dbms_job has been running for nearly thirty days.

The number of total time keeps rising, but I can't find any info of the running job.

When I execute sql that is "select * from dba_jobs;", the result shows no job is running.

I set it to broken, but it doesn't work.

How can I stop this dbms_job safely?

the job's information

mike.jiang
  • 207
  • 1
  • 6
  • 16
  • Is there any specific reason why you're using the ancient dbms_job instead of dbms_scheduler? dbms_scheduler is more maintainable, easier to use and overall much superior IMHO. – Frank Schmitt Dec 06 '16 at 07:48

3 Answers3

7

DBA_JOBS lists all the jobs created in the database. You could use DBA_JOBS_RUNNING view to list all jobs that are currently running in the instance.

When I execute sql that is "select * from dba_jobs;", the result shows no job is running.

This shows all the jobs created using DBMS_JOBS package. The job might have created using DBMS_SCHEDULER package.

SQL> select job from user_jobs;

       JOB
----------
        99

I have created only one job using DBMS_JOBpackage which is called 99. Others are created using DBMS_SCHEDULERpackage which can be seen using:

SQL> select job_name from user_scheduler_jobs;

As per the documentation-

Stopping a Job

Note that, once a job is started and running, there is no easy way to stop the job.

Its not easy to stop the job.

Don't know which version of Oracle database are you using. But starting with Oracle 10g You use the following query to list the scheduled jobs.

SQL>select * from all_scheduler_jobs;

ALL_SCHEDULER_JOBS

ALL_SCHEDULER_JOBS displays information about the Scheduler jobs accessible to the current user.More...


Use the following query to find the currently running job.

SQL>select job_name, session_id, running_instance, elapsed_time, cpu_used
from user_scheduler_running_jobs;

And to stop-

SQL>EXEC DBMS_SCHEDULER.STOP_JOB (job_name => 'JOB_NAME');
atokpas
  • 3,231
  • 1
  • 11
  • 22
  • 2
    `xxx_JOBS` and `xxx_SCHEDULER_JOBS` are two completely different beasts. The former has been around for ages and is outdated, the latter is the successor. – Frank Schmitt Dec 06 '16 at 07:47
  • 1
    If the graceful stop doesn't work, you may also try to stop it immediately using the force parameter: `EXEC DBMS_SCHEDULER.STOP_JOB (job_name => 'JOBNAME', force => TRUE);`. – fuggi Mar 11 '20 at 10:42
2

Short of killing the session, you're out of luck.

From the Documentation:

Note that, once a job is started and running, there is no easy way to stop the job.

That's one of the many reasons why you shouldn't use dbms_job anymore. Use dbms_scheduler instead.

Frank Schmitt
  • 30,195
  • 12
  • 73
  • 107
0

checkout SID in "select * from DBA_JOBS_RUNNNG" thats session ID kill that session and thats it changes will be rolled back, so it can take "more" time to abort than letting it finish if that bulk update was 90% complete....