Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Mario.

Asked: March 30, 2017 - 2:47 pm UTC

Last updated: February 16, 2022 - 12:09 am UTC

Version: 12.1.0.2.0

Viewed 10K+ times! This question is

You Asked

Hello, I have the following problem since 4 days, where jobs have stopped running.

Last Successful login time: Thu Mar 30 2017 15:53:42 +02:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select count(1) from dba_scheduler_running_jobs;

COUNT(1)
----------
22

SQL> select session_id, slave_process_id, slave_os_process_id, elapsed_time from dba_scheduler_running_jobs;

SESSION_ID SLAVE_PROCESS_ID SLAVE_OS_PRO ELAPSED_TIME
---------- ---------------- ------------ ---------------------------------------------------------------------------
+004 11:40:15.40
+004 12:40:13.62
+004 12:40:13.37
+004 11:40:15.46
+004 11:36:23.24
+004 11:42:15.39
+004 11:39:37.46
+004 11:40:15.46
+004 11:36:15.45
+004 11:40:14.59
+004 11:50:15.26
+004 12:40:13.90
+004 11:36:15.43
+004 12:40:12.79
+004 12:40:13.66
+004 11:40:14.92
+004 11:36:43.46
+004 12:40:13.64
+004 11:40:14.89
+004 11:36:55.39
+004 11:40:14.77
+004 12:40:13.89

22 rows selected.

All of this jobs have been "running" for more than 4 days, but they do not have the session ID. When I try to disable or drop these jobs, I am not able, even with the force => TRUE.

I have to mention that, for some unknown reason (probably Dayligth Saving Time change), the next_run_date value is < sysdate in the dba_scheduler_jobs table.

SQL> select next_run_date, last_start_date from dba_scheduler_jobs where job_name in (select job_name from dba_scheduler_running_jobs) and next_run_date < sysdate;

NEXT_RUN_DATE LAST_START_DATE
--------------------------------------------------------------------------- ---------------------------------------------------------------------------
25-MAR-17 08.04.00.060878 PM -07:00 25-MAR-17 08.04.00.944973 PM -07:00
25-MAR-17 08.04.00.000000 PM -07:00 25-MAR-17 08.04.00.928087 PM -07:00
25-MAR-17 08.03.50.000000 PM -07:00 25-MAR-17 08.03.53.137249 PM -07:00
25-MAR-17 08.03.30.066319 PM -07:00 25-MAR-17 08.03.32.921065 PM -07:00
25-MAR-17 08.03.20.066234 PM -07:00 25-MAR-17 08.03.20.984738 PM -07:00
25-MAR-17 08.00.36.817393 PM -07:00 25-MAR-17 08.00.38.914774 PM -07:00
25-MAR-17 07.50.00.912601 PM -07:00 25-MAR-17 07.50.01.121851 PM -07:00
26-MAR-17 04.00.00.988940 AM +01:00 26-MAR-17 04.00.01.794030 AM +01:00
26-MAR-17 05.00.00.946032 AM +02:00 26-MAR-17 05.00.01.486675 AM +02:00
26-MAR-17 04.00.00.910633 AM +01:00 26-MAR-17 04.00.01.454065 AM +01:00
26-MAR-17 04.00.00.909658 AM +01:00 26-MAR-17 04.00.01.611223 AM +01:00
26-MAR-17 05.00.00.125006 AM +02:00 26-MAR-17 05.00.00.918680 AM +02:00
26-MAR-17 04.00.00.119020 AM +01:00 26-MAR-17 04.00.00.917832 AM +01:00
26-MAR-17 04.00.00.043479 AM +01:00 26-MAR-17 04.00.00.976956 AM +01:00
26-MAR-17 03.58.00.898672 AM +01:00 26-MAR-17 03.58.00.987649 AM +01:00
26-MAR-17 04.00.00.714678 AM +02:00 26-MAR-17 04.00.03.587298 AM +02:00
26-MAR-17 04.00.00.532567 AM +02:00 26-MAR-17 04.00.03.006812 AM +02:00
26-MAR-17 04.00.00.414492 AM +02:00 26-MAR-17 04.00.02.756587 AM +02:00
26-MAR-17 04.00.00.406513 AM +02:00 26-MAR-17 04.00.02.739345 AM +02:00
26-MAR-17 04.00.00.275335 AM +02:00 26-MAR-17 04.00.02.722120 AM +02:00
26-MAR-17 04.00.00.267387 AM +02:00 26-MAR-17 04.00.02.481094 AM +02:00
26-MAR-17 04.00.00.252430 AM +02:00 26-MAR-17 04.00.02.490174 AM +02:00

22 rows selected.

and Connor said...

Try the following

1) stop the scheduler

SQL> exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED','TRUE');

2) for each 'running' job

SQL> exec dbms_scheduler.stop_job('SCOTT.MY_JOB',TRUE);
SQL> exec dbms_scheduler.drop_job('SCOTT.MY_JOB',TRUE);

3) restart the scheduler

SQL> exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED','FALSE');

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Good

Mario Measic, April 03, 2017 - 7:36 am UTC

Thank you, Connor.

A reader, May 06, 2020 - 1:27 pm UTC

Thank you so much

SHOW

fbuss, July 07, 2020 - 12:13 am UTC


Connor McDonald
July 07, 2020 - 5:55 am UTC

...me the money?

Same issue with DBA_JOBS

Samir Pophalkar, February 15, 2022 - 7:10 am UTC

>select * from dba_jobs_running;

SID JOB FAILURES LAST_DATE LAST_SEC THIS_DATE THIS_SEC INSTANCE
---------- ---------- ---------- --------- -------- --------- -------- ----------
35 660 0 15-FEB-22 09:19:11 15-FEB-22 09:19:11 0
2437 656 0 15-FEB-22 09:13:06 15-FEB-22 09:13:06 0
3053 659 0 15-FEB-22 11:10:39 15-FEB-22 11:10:39 0
3049 657 0 15-FEB-22 09:19:17 15-FEB-22 09:19:17 0
414 658 0 15-FEB-22 11:52:51 15-FEB-22 11:52:51 0
482 0 14-FEB-22 12:44:29 14-FEB-22 12:44:29 0


>select sid from dba_jobs_running where job=482;

SID
----------


above job 482 is showing running with no SID. Hence further execution of this job is not happening. How to release this job from dba_jobs_running, so that next execution will happen automatically
Connor McDonald
February 16, 2022 - 12:09 am UTC

Is this 19c? If it is, then "old style" jobs are silently being managed as scheduler jobs, so the same soluition might apply.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.