How to stop Oracle dbms_job

Question

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?

Answer 1

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