Example of Oracle Code: Stopping an SQL Job

The benefit of using graphical tools that imitate sqlplus is that they actually halt the script instead of submitting the rest of the script as shell commands when pasted into SQL Plus running in a console window. Alternative solution 2: If you prefer not to generate an exception, you could attempt something like this (not tested): Some individuals dislike GOTO statements as they can result in convoluted code if misused, but in straightforward scenarios like this (assuming you don’t want to raise an exception), they are effective in my opinion.


Solution 1:

The database displays a list of all created jobs. To see the jobs currently running in the instance, you can utilize the view provided by

DBA_JOBS_RUNNING

.

The execution of the SQL query “select * from dba_jobs;” does not display any running jobs in the result.

The display presents the jobs that were created using the

DBMS_JOBS

package, which could have also been created using the

DBMS_SCHEDULER

package.

SQL> select job from user_jobs;
       JOB
----------
        99

I have utilized the

DBMS_JOB

package, specifically named

99

, to create a single job. The remaining jobs, however, are created using the

DBMS_SCHEDULER

package, which can be observed by utilizing:

SQL> select job_name from user_scheduler_jobs;

As per the documentation-

Stopping a Job

Please be aware that once a job has commenced and is in progress, there is no straightforward method to halt the job.

Its not easy to stop the job.

Uncertain about the Oracle database version you are using, but beginning from Oracle 10g, you can utilize the following query to display the scheduled jobs.

SQL>select * from all_scheduler_jobs;
ALL_SCHEDULER_JOBS

The ALL_SCHEDULER_JOBS feature provides details on Scheduler jobs that are accessible to the current user.

To locate the job that is currently running, utilize the provided query.

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


Solution 2:


Unless you terminate the session, you won’t have any luck.

From the Documentation:

Please be aware that once a job has been initiated and is in progress, there is no simple method to halt the job.

Using

dbms_job

is not recommended for several reasons, and that is why it is suggested to use

dbms_scheduler

instead.


Solution 3:


Retrieve the session ID from the ‘DBA_JOBS_RUNNING’ table using the ‘SELECT *’ statement. Terminate the session associated with that ID, and as a result, any changes made will be rolled back. It is important to note that if the bulk update was 90% complete, it may take longer to abort the process than to let it finish.

Frequently Asked Questions