DBMS Scheduler With No Session Id (Doc ID 2628776.1)

Last updated on JULY 20, 2024

Applies to:

Goal

 All of these 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.

For some unknown reason (probably Daylight Saving Time change), the next_run_date value is < sysdate in the dba_scheduler_jobs table.

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, secondary_process_id, secondary_os_process_id, elapsed_time from dba_scheduler_running_jobs;

SESSION_ID SECONDARY_PROCESS_ID SECONDARY_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.

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

22 rows selected.

Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.