May 12

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
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 http://oraclespin.wordpress.com/2010/05/08/dba_scheduler_running_jobs/

May 12

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 it was run
SQL> SELECT job, interval, next_date FROM dba_jobs where job = 5;
JOB NEXT_DATE
———- ——————–
5 19-MAR-2012 10:00:00

Setting a job to run every hour on the 1/2 hour
SQL> exec dbms_job.interval(5, 'TRUNC(SYSDATE, ''HH'')+(1.5/24)');
SQL> commit;

Setting a job to run every hour after the last time the job was started
SQL> exec dbms_job.interval(5, 'SYSDATE+(1/24)');
SQL commit;
– assume job was run and this SQL was run after it was run, the time NEXT_DATE would be a hour after the job was last started.
SQL> SELECT job, interval, next_date FROM dba_jobs where job = 5;
JOB NEXT_DATE
———- ——————–
5 15-MAR-2012 22:45:00

May 12

Using sys.dbms_iob one can manage jobs in dba_jobs.

DBMS_JOB allows one to create/manage jobs under user who has logged but using sys.dbms_ijob one can manage jobs all jobs scheduled in DBA_JOBS.

Here are some of the functions available in sys.dbms_ijob.
To execute/run job: You don’t have to be an owner of the job
SQL> exec sys.dbms_ijob.run(5);

If one tried executing the job not owned by the user using DBMS_JOB, one can’t run it and you will get the following error “ORA-23421: job number 21 is not a job in the job queue”

To enable/disable job:
-- to disable a job. Note one should set this when the job is not running by checking DBA_JOBS_RUNNING, because if the job is running you will have to wait till it finishes before disabling it and it has to be followed by a commit.
SQL> exec sys.dbms_ijob.broken(5, true);
SQL> commit;

-- Note the next_date value it initialized when the job is broken.
SQL> select broken, next_date from dba_jobs where job = 5;

B TO_CHAR(NEXT_DATE,’D
- ——————–
Y 01-JAN-4000 00:00:00

-- to enable a job, note it has to be followed by a commit and after the job is enabled the NEXT_DATE is updated based on the value of interval.
SQL> exec sys.dbms_ijob.broken(5, false);
SQL> commit;

SQL> select broken, next_date from dba_jobs where job in ( 5 );

B TO_CHAR(NEXT_DATE,’D
- ——————–
N 11-MAR-2012 19:31:45

To change the next date the job runs, again note if the job is running when one modifies this, it will get reset to the next interval instead of the value specified.
SQL> exec sys.dbms_ijob.next_date(5, sysdate + 1);
SQL commit;

SQL> select next_date from dba_jobs where job in ( 5 );

B TO_CHAR(NEXT_DATE,’D
- ——————–
N 12-MAR-2012 19:41:27

To change the interval
Example below changes the interval to run every Monday at midnight
SQL> exec sys.dbms_ijob.interval(5, 'TRUNC(NEXT_DAY(sysdate, ''MONDAY''))');
PL/SQL procedure successfully completed.
SQL> commit;

May 12

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 the parameter in memory, which will suspend jobs from starting
SQL> alter system set job_queue_processes=0 scope=memory;

System altered.

May 12

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);

May 12

How to submit a new job using dbms_job?

Using dbms_job one can schedule a job, in the following example schedules a job to run every 5 minutes starting from current time. The next interval can be fixed time or it can be based on the time when the last run finished, in the example below the interval is time when the last run/time when the job finishes so the next run would occur 5 minutes after the last run. The owner, priv user and log user of the job created would be the user who executed the dbms_job.submit.
SQL> set serveroutput on
SQL> declare
job_id number;
begin
dbms_job.submit(job_id, 'dbms_lock.sleep(100);', sysdate, 'sysdate+5/(24*60)');
dbms_output.put_line('Job: ' || job);
end;
/
Job: 21
PL/SQL procedure successfully completed.

--Make sure you run commit to schedule the job.
SQL> commit;

-- To see the time when the job last run and next time it will run
SQL> select job, last_date, last_sec, next_date, next_Sec from dba_jobs where job in (21);

JOB LAST_DATE LAST_SEC NEXT_DATE NEXT_SEC
———- ——— ——– ——— ——–
21 19-FEB-12 19:57:43 19-FEB-12 20:02:43

May 09

dba_scheduler_running_jobs

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 and vs.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

Apr 20

How to monitor oracle jobs

To show details on job run:

select log_date, job_name, status, req_start_date, actual_start_date, run_duration
from dba_scheduler_job_run_details

To show running jobs:

select job_name, session_id, running_instance, elapsed_time, cpu_used
from dba_scheduler_running_jobs;

To show job history:

select log_date , job_name, status
from dba_scheduler_job_log;

show all schedules:

select schedule_name, schedule_type, start_date, repeat_interval
from dba_scheduler_schedules;

show all jobs and their attributes:

select * from dba_scheduler_jobs

show all program-objects and their attributes

select * from dba_scheduler_programs;

show all program-arguments:

select * from dba_scheduler_program_args;

Nov 26

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.

– check job status
SQL> select job_name, enabled from DBA_SCHEDULER_JOBS WHERE job_name = ‘GATHER_STATS_JOB’;

JOB_NAME ENABL
————————- —–
GATHER_STATS_JOB TRUE

Nov 26

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 haven’t succeeded
SELECT job_name, log_date, status, actual_start_date, run_duration, cpu_used FROM dba_scheduler_job_run_details where status ‘SUCCEEDED’;