Database Software

Oracle SE 12.1

I have a job that executes a stored procedure. It all runs fine for a few weeks, then stops and I cannot restart it. (The last time this happened, I had to recreate the job.). its meant to run every 10 minutes..

I try disabling and re-enabling it, but get:

SQL> begin

  2   DBMS_SCHEDULER.DISABLE('GET_ORACLE_POPS');

  3  end;

  4  /

ORA-27478: job "SYS"."GET_ORACLE_POPS" is running

ORA-06512: at "SYS.DBMS_ISCHED", line 4517

ORA-06512: at "SYS.DBMS_SCHEDULER", line 2915

But when I look at the log, it shows that the last time it ran was yesterday afternoon.

Ive tried dbms_scheduler.set_scheduler_attribute ( 'SCHEDULER_DISABLED', 'FALSE' );

I have Job Queue processes set to 12, and when I check: select owner, job_name, session_id, elapsed_time from dba_scheduler_running_jobs;

I get my one running job as above..

owner, job_name, session_id, elapsed_time

I notice that the elapsed time is  20 hrs, and looks like the job is stuck in a running state, but not doing anything. It should normally take a few seconds.. (Its connecting to a mail server, and retrieving email attachments.)

If I run the plsql process manually that the job executes, it executes fine..

Its possible that for some reason, its hung trying to connect. This being the case, how do I kill the running job, so I can restart it?

Regards

Richard