How to stop a job scheduled in DBMS_SCHEDULER?

Using sys.dbms_scheduler.STOP_JOB one can stop scheduled job. 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... Continue Reading →


dba_jobs and interval

Few examples of some scenarios of setting intervals for dba_jobs. Note: Updating the interval will not update the NEXT_DATE run of the job until the job runs. Setting a job to run every monday at 10:00 am SQL> exec dbms_job.interval(5, 'TRUNC(NEXT_DAY(SYSDATE, ''MONDAY''))+(10/24)'); SQL> commit; -- assume job was run and this SQL was run after... Continue Reading →

How to suspend all jobs from executing in dba_jobs?

By setting the value of 0 to the parameter "job_queue_processes" one can suspend all jobs from executing in DBA_JOBS. The value of this parameter can be changed without instance restart. -- see current value of the parameter SQL> show parameter job_queue_processes; NAME TYPE VALUE ------------------------------------ ----------- ----------- job_queue_processes integer 1000 -- Set the value of... Continue Reading →

How to see the jobs currently being executed?

Using dba_jobs_running it will show the all jobs executing. SQL> select djr.sid, djr.job, djr.failures, djr.this_date, djr.this_sec, dj.what from dba_jobs_running djr, dba_jobs dj where djr.job = dj.job; SID JOB FAILURES THIS_DATE THIS_SEC ---------- ---------- ---------- -------------------- -------- WHAT ---------------------------------------------------------------------------------------------------- 68 21 0 19-feb-2012 21:09:34 21:09:34 dbms_lock.sleep(100);


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.... Continue Reading →

How to enable/disable a scheduled job?

Using the package DBMS_SCHEDULER one can enable/disable jobs. To disable job: This disables the job from running SQL> exec dbms_scheduler.disable('GATHER_STATS_JOB'); PL/SQL procedure successfully completed. -- check job status SQL> select job_name, enabled from DBA_SCHEDULER_JOBS WHERE job_name = 'GATHER_STATS_JOB'; JOB_NAME ENABL ------------------------- ----- GATHER_STATS_JOB FALSE To enable job: SQL> exec dbms_scheduler.enable('GATHER_STATS_JOB'); PL/SQL procedure successfully completed. --... Continue Reading →

How to find jobs currently running or history about the jobs?

In 10g one can find the jobs that are currently running by querying the following view SELECT job_name, session_id, running_instance, elapsed_time, cpu_used FROM dba_scheduler_running_jobs; Also one can use the following view to find the history details of job that has run. SELECT job_name, log_date, status, actual_start_date, run_duration, cpu_used FROM dba_scheduler_job_run_details; To find the jobs that... Continue Reading →

Blog at

Up ↑