CODEWITHSUNDEEP
databaseoracle-databasedbms-job
mike.jiang
mike.jiang

Reputation: 227

How to stop Oracle dbms_job

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

Upvotes: 6

Views: 107742

Answers (3)

user12722481
user12722481

Reputation: 1

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

Upvotes: 0

atokpas
atokpas

Reputation: 3351

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

Upvotes: 8

Frank Schmitt
Frank Schmitt

Reputation: 30775

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.

Upvotes: 3

Related Questions