Using sys.dbms_scheduler.STOP_JOB one can stop scheduled job.
$ sqlplus / as sysdba
SQL> exec sys.dbms_scheduler.STOP_JOB(job_name=>'SYS.ORA$AT_OS_OPT_SY_12856', force=>true);
Output:
PL/SQL procedure successfully completed.
If it can’t find the job then you may see the following error, in the example below I hadn’t specified the user to it was looking for the job as the current user.
ORA-27475: “DBAUSER.ORA$AT_OS_OPT_SY_12856” must be a job
ORA-06512: at “SYS.DBMS_ISCHED”, line 199
ORA-06512: at “SYS.DBMS_SCHEDULER”, line 557
ORA-06512: at line 1
To see jobs running ----
Using DBA_SCHEDULER_RUNNING_JOBS one can see jobs currently running. In the
example below it shows the oracle session id and corresponding OS process id.
22:20:51
sys@TESTDB> select job_name, session_id from dba_scheduler_running_jobs;
JOB_NAME SESSION_ID
—————————— ———-
GATHER_STATS_JOB 364
1 row selected.
sys@TESTDB> select program from v$session where sid = 364;
PROGRAM
————————————————oracle@hostname (J002)
1 row selected.
sys@TESTDB> select vs.program, spid from v$session vs, v$process vp where vs.sid = 364 andvs.paddr = vp.addr
PROGRAM SPID
———————————————— ————oracle@hostname (J002) 1642698
1 row selected.
Oracle j000 shadow processes are DBMS_SCHEDULER sessions
sys@TESTDB> !ps -ef | grep 1642698
oracle 1642698 1 120 00:00:07 – 1071:14 ora_j002_TESTDB
oracle 13836458 11677872 2 22:23:06 pts/5 0:00 grep 1642698
