you are a new oracle dba and you already know a little bit about oracle scheduler jobs. If so then you can find in this article some commands on how to create the scheduler jobs. firstly you should know that that there are 2 different kinds of jobs. The first type created using the dbms_job package and the second type using the dbms_scheduler package.
until oracle 9i the oracle DBA’s used to create oracle jobs using the dbms_job package. From oracle 10g onwards DBA’s most of the time use the dbms_scheduler package to create jobs and in certain situations like when distributed tranasactions are involved the jobs are created using dbms_job package.
In this article you will learn about the following
1)syntax for creating a scheduler job , setting its attributes and how to check whether it is running or not.
2)Different categories of oracle scheduler jobs like programs , jobs, schedules, job classes , windows , windows groups.
3)sometimes you have jobs that are created using the dbms_job package and for some reason those jobs hang and even if you try to kill the session it wont help.you will see in this article one way handle that situation.
–creating new job
begin
dbms_scheduler.create_job(
job_name => ‘ENGINE_JOB_SCHEDULE’
,job_type => ‘PLSQL_BLOCK’
,job_action => ‘begin dbms_stats.gather_schema_stats(ownname =>”ENGINE
”,estimate_percent => 30,cascade=>true,method_opt=> ”for all columns size 1”,
degree => 5 , options => ”GATHER STALE ”); end; ‘
,start_date => trunc(sysdate + 1) + 6/24
,repeat_interval => ‘FREQ=DAILY’
,enabled => TRUE
,comments => ‘customized stats gathering for risk engine’);
end;
—changing a job attribute
dbms_scheduler.set_attribute(‘ENGINE_JOB_SCHEDULE’,’job_action’, ‘begin dbms_stats.gather_schema_stats(ownname =>”ENGINE
”,estimate_percent => 30,cascade=>true,method_opt=> ”for all columns size 1”,
degree => 5 , options => ”GATHER STALE”); end; ‘);
–running a job immediately
dbms_scheduler.run_job(‘ENGINE_JOB_SCHEDULE’);
–TO FIND OUT THE JOB DETAILS LIKE WHEN IT HAS STARTED , HOW LONG IT HAS RUN ETC
select log_date
, job_name
, status
, req_start_date
, actual_start_date
, run_duration
from dba_scheduler_job_run_details
WHERE JOB_NAME LIKE ‘%ENGINE%’
2)different oracle scheduler jobs concepts
Job scheduling from Oracle 10g with dbms_scheduler
In Oracle 10g the DBMS_JOB package is replaced by the DBMS_SCHEDULER package. The DBMS_JOB package is now depricated and in Oracle 10g it’s only provided for backward compatibility. From Oracle 10g the DBMS_JOB package should not be used any more, because is could not exist in a future version of Oracle.
With DBMS_SCHEDULER Oracle procedures and functions can be executed. Also binary and shell-scripts can be scheduled.
Rights
If you have DBA rights you can do all the scheduling. For administering job scheduling you need the priviliges belonging to the SCHEDULER_ADMIN role. To create and run jobs in your own schedule you need the ‘CREATE JOB’ privilege.
With DBMS_JOB you needed to set an initialization parameter to start a job coordinator background process. With Oracle 10g DBMS_SCHEDULER this is not needed any more.
If you want to user resource plans and/or consumer groups you need to set a system parameter:
ALTER SYSTEM SET RESOURCE_LIMIT = TRUE;
Getting started quickly
To quickly get a job running, you can use code like this:
begin
dbms_scheduler.create_job(
job_name => 'DEMO_JOB_SCHEDULE'
,job_type => 'PLSQL_BLOCK'
,job_action => 'begin package.procedure(''param_value''); end; '
,start_date => '01/01/2006 02:00 AM'
,repeat_interval => 'FREQ=DAILY'
,enabled => TRUE
,comments => 'Demo for job schedule.');
end;
/
This schedules a pl/sql block to be executed daily starting 1/1/2006 02:00 AM.
You can schedule things like this, but DBMS_SCHEDULER can reuse components.
You can build a schedule using components like program, schedule, job, job class and window. We will now discuss these components in detail.
Program
The program component represents program-code that can be executed. This program code can have parameters. Code example
begin
dbms_scheduler.create_program (
program_name => 'DEMO_JOB_SCHEDULE'
,program_type => 'STORED_PROCEDURE'
,program_action => 'package.procedure'
,number_of_arguments => 1
,enabled => FALSE
,comments => 'Demo for job schedule.');
dbms_scheduler.define_program_argument (
program_name => 'DEMO_JOB_SCHEDULE'
,argument_position => 1
,argument_name => 'kol1'
,argument_type => 'VARCHAR2'
,default_value => 'default'
);
dbms_scheduler.enable(name => 'DEMO_JOB_SCHEDULE');
end;
/
The parameter program_type can have one of the following values: ‘PLSQL_BLOCK’, ‘STORED_PROCEDURE’,’EXECUTABLE’.
dbms_scheduler also allows to execute shell scripts (Windows: *.bat files) and executables.
Schedule
A schedule defines the frequence and date/time specifics of the start-time for the job.
example code
begin
dbms_scheduler.create_schedule(
schedule_name => 'DEMO_SCHEDULE'
, start_date => '01/01/2006 22:00:00'
, repeat_interval => 'FREQ=WEEKLY'
, comments => 'Weekly at 22:00');
END;
/
To drop the schedule:
begin
dbms_scheduler.drop_schedule(
schedule_name => 'DEMO_SCHEDULE'
, force => TRUE );
end;
/
Calendar expresions can have one of these values: ‘Yearly’,’Monthly’,’Weekly’,’Daily’,’Hourly’,’Minutely’,’Secondely’
Job
A job defines when a specific task will be started. This can be done by assigning a program to one or more schedules (or to a specific date/time). A job can belong to only 1 job class. Code example
begin
dbms_scheduler.create_job(
job_name => 'DEMO_JOB1'
, program_name =>'DEMO_JOB_SCHEDULE'
, schedule_name =>'DEMO_SCHEDULE'
, enabled => FALSE
, comments => 'Run demo program every week at 22:00');
dbms_scheduler.set_job_argument_value(
job_name => 'DEMO_JOB1'
, argument_position => 1
, argument_value => 'param1');
dbms_scheduler.enable('DEMO_JOB1');
commit;
end;
/
Or start shell script
begin
dbms_scheduler.create_job
(
job_name => 'RUN_SHELL1',
schedule_name => 'DEMO_SCHEDULE',
job_type => 'EXECUTABLE',
job_action => '/home/test/run_script.sh',
enabled => true,
comments => 'Run shell-script'
);
end;
/
Monitoring job-scheduling
Jobs can be monitored using Oracle Enterprise Manager 10g. It’s also possible to use a number of views that have been created in Oracle 10g. We will discuss some of these views here.
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;
select log_date ,JOB_NAME, STATUS, ERROR# from dba_scheduler_job_run_details where JOB_NAM
E like '%REFRESH_SPIDER%' and log_date > sysdate – 5
SELECT * FROM dba_scheduler_programs;
SELECT * FROM dba_scheduler_schedules;
select log_date
, job_name
, status
, req_start_date
, actual_start_date
, run_duration
from dba_scheduler_job_run_details
SELECT * FROM dba_scheduler_jobs;
SELECT *
FROM dba_scheduler_window_groups;
SELECT *
FROM dba_scheduler_windows;
These below ones are some examples of setting jobs
This will run your job: dbms_job.submit(:v_JobNo, 'arbitraryprod1(oneinput);', TRUNC(SYSDATE)+2/24, 'TRUNC(SYSDATE+1)+2/24'); To stop it, you can execute: exec dbms_job.broken(jobnumber, TRUE); And to turn it back on: exec dbms_job.broken(JOB=>&job_no, NEXT_DATE=>TRUNC(SYSDATE)+2/24, broken=>FALSE
3)nice article on how to kill oracle jobs that are very difficult to kill
Killing the Oracle DBMS_JOB
James F. Koopmann, jkoopmann@dbdoctor.net
select * from dba_jobs where job= 259;
SELECT SID, TYPE, ID1, ID2
FROM V$LOCK
WHERE TYPE = ‘JQ’;
select * from dba_jobs_running
Take control of Oracle’s queue with a step by step approach to getting rid of those pesky DBMS_JOBs.
Lets face it, Oracle’s job scheduling facility is a wonderful tool for scheduling Oracle related jobs without having to maintain a cron job on Unix or an AT job in windows. It is also very robust and reliable. It is that very reliability and robustness that gives many of us our problems.
If you have any form of jobs running on your system, you will at one time or another come across the issue of a run-away job that just doesn’t seem to want to end. Or maybe you will try and shutdown the database only to find out that it is waiting to complete a job. I would like to offer some help in the management of those job queues when they just don’t seem to want to end or go away.
A while back I needed to find information on how to clear the job queue for jobs running with no apparent end in sight. Some had hung, while others just were taking a bad access path to data. I needed to bring down these jobs, do a bit of tuning and then restart the jobs. Well, to my amazement, there wasn’t very much information out on the web that gave good insight into this process. Basically the method suggested was to first break the job and then issue an ALTER SYTEM KILL SESSION command. This method does not always work and unfortunately–never on my system, for the jobs I had. I then called Oracle support and basically got the same answer as I found out on the web. They did give me one added piece of information. They said, if the ALTER SYSTEM KILL SESSION didn’t work, I was supposed to bounce my database in order to bring down the job queue processes. First of all, this wasn’t an option and when I did get the opportunity to bounce the database box, many of the jobs seemed to come right back as strong as ever.
Before writing this article I did another quick search on the topic of killing dbms_jobs and to my amazement there still wasn’t much good information out there. This is why I want to share my method, so that you won’t be stuck up against the wall with this problem and nowhere to turn, as I was.
Lets first go through a few different methods of viewing the information about job queues.
Viewing scheduled dbms_jobs
When looking at what jobs have been scheduled, there is really only one view that you need to go to. The dba_jobs view contains all of the information you need, to see what has been scheduled, when they were last run, and if they are currently running. Use the following simple script to take a look. Bear with me on the sub-select, I will build on this query as we go on in the presentation.
scheduled_dbms_jobs.sql
set linesize 250
col log_user for a10
col job for 9999999 head 'Job'
col broken for a1 head 'B'
col failures for 99 head "fail"
col last_date for a18 head 'Last|Date'
col this_date for a18 head 'This|Date'
col next_date for a18 head 'Next|Date'
col interval for 9999.000 head 'Run|Interval'
col what for a60
select j.log_user,
j.job,
j.broken,
j.failures,
j.last_date||':'||j.last_sec last_date,
j.this_date||':'||j.this_sec this_date,
j.next_date||':'||j.next_sec next_date,
j.next_date - j.last_date interval,
j.what
from (select dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES,
dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC,
dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
from dba_jobs dj) j;
What Jobs are Actually Running
A simple join to the dba_jobs_running view will give us a good handle on the scheduled jobs that are actually running at this time. This is done by a simple join through the job number. The new column of interest returned here is the sid which is the identifier of the process that is currently executing the job.
running_jobs.sql
set linesize 250
col sid for 9999 head 'Session|ID'
col log_user for a10
col job for 9999999 head 'Job'
col broken for a1 head 'B'
col failures for 99 head "fail"
col last_date for a18 head 'Last|Date'
col this_date for a18 head 'This|Date'
col next_date for a18 head 'Next|Date'
col interval for 9999.000 head 'Run|Interval'
col what for a60
select j.sid,
j.log_user,
j.job,
j.broken,
j.failures,
j.last_date||':'||j.last_sec last_date,
j.this_date||':'||j.this_sec this_date,
j.next_date||':'||j.next_sec next_date,
j.next_date - j.last_date interval,
j.what
from (select djr.SID,
dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES,
dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC,
dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
from dba_jobs dj, dba_jobs_running djr
where dj.job = djr.job ) j;
What Sessions are Running the Jobs
Now that we have determined which jobs are currently running, we need to find which Oracle session and operating system process is accessing them. This is done through first joining v$process to v$session by way of paddr and addr which is the address of the processs that owns the sessions, and then joining the results back to the jobs running through the sid value. The new columns returned in our query are spid which is the operating system process identifier and serial# which is the session serial number.
session_jobs.sql
set linesize 250
col sid for 9999 head 'Session|ID'
col spid head 'O/S|Process|ID'
col serial# for 9999999 head 'Session|Serial#'
col log_user for a10
col job for 9999999 head 'Job'
col broken for a1 head 'B'
col failures for 99 head "fail"
col last_date for a18 head 'Last|Date'
col this_date for a18 head 'This|Date'
col next_date for a18 head 'Next|Date'
col interval for 9999.000 head 'Run|Interval'
col what for a60
select j.sid,
s.spid,
s.serial#,
j.log_user,
j.job,
j.broken,
j.failures,
j.last_date||':'||j.last_sec last_date,
j.this_date||':'||j.this_sec this_date,
j.next_date||':'||j.next_sec next_date,
j.next_date - j.last_date interval,
j.what
from (select djr.SID,
dj.LOG_USER, dj.JOB, dj.BROKEN, dj.FAILURES,
dj.LAST_DATE, dj.LAST_SEC, dj.THIS_DATE, dj.THIS_SEC,
dj.NEXT_DATE, dj.NEXT_SEC, dj.INTERVAL, dj.WHAT
from dba_jobs dj, dba_jobs_running djr
where dj.job = djr.job ) j,
(select p.spid, s.sid, s.serial#
from v$process p, v$session s
where p.addr = s.paddr ) s
where j.sid = s.sid;
Now that we have a good handle on how we can look at the jobs and the key columns involved, let’s go through the steps needed to bring down a job. The following is a 5 to 11 step process that should solve all of your problems.
Bringing Down a DBMS_JOB
1. Find the Job You Want to Bring Down
In order to do anything you first need to find the job that is giving you a headache. Go ahead and run the running_jobs.sql. This will give you the prime information, job, sid, serial#, and spid, for the following actions in bringing down the job.
2. Mark the DBMS_JOB as Broken
Use the following command for the job that you have to deal with.
SQL> EXEC DBMS_JOB.BROKEN(job#,TRUE);
All this command does is mark the job so that if we get it to stop, it won’t start again. Let’s make one thing perfectly clear, after executing this command the job is still running.
As a side note, if you are trying to shut down a database with jobs that run throughout the day, they may hinder your attempts to bring down the database cleanly. This is a wonderful command to make sure no jobs are executing during the shutdown process. Just be aware that you will need to mark the jobs as unbroken when the database comes back up, more on that later.
3. Kill the Oracle Session
Since the job is still running and it isn’t going to end soon, you will need to kill the Oracle session that is executing the job. Use the following command for to kill the job.
ALTER SYSTEM KILL SESSION 'sid,serial#';
4. Kill the O/S Process
More often than not the previous step will still leave the job attached to the database and still running. When this happens you will need to go out to the operating system level and get rid of the process that has spawned from the running job. In order to do this you must login to the database box and issue the following command, depending on the type of operating system you have.
For Windows, at the DOS Prompt: orakill sid spid
For UNIX at the command line> kill –9 spid
The orakill is an Oracle command, while kill is a Unix command.
5. Check if the Job is Still Running
Re-run the session_jobs.sql script to see if you have gotten rid of the job. If you have there is no reason to go further. Usually steps 1 through 4 will be sufficient to get rid of a job but when the job is running wild you will have to continue with steps 6 through 11 which describes a process for bouncing the job queue process.
6. Determine the Current Number of Job Queue Processes
SQL> col value for a10
SQL> select name,value from v$parameter where name = 'job_queue_processes';
7. Alter the Job Queue to Zero
SQL> ALTER SYSTEM SET job_queue_processes = 0;
This will bring down the entire job queue processes.
8. Validate that No Processes are Using the Job Queue
Re-run the session_jobs.sql script to see if any jobs are still running. Since we have given a hard stop to the job queue and issued the kill commands, you can now wait until no more jobs are running. After all the jobs have quit running, you can do whatever maintenance or tuning you need to do before proceeding.
9. Mark the DBMS_JOB as Not Broken
You can now reset the broken job to not broken so they can run again. Just issue the command.
SQL>EXEC DBMS_JOB.BROKEN(job#,FALSE):
10. Alter the Job Queue to Original Value
Set the job queue to its’ original value so that the jobs can run again.
ALTER SYSTEM SET job_queue_processes = original_value;
11. Validate that DBMS_JOB Is Running
To make sure everything is back to normal, re-run the above scripts to validate that jobs are scheduled, not broken, and are executing with the next and last dates columns changing.
Oracle have given us a great tool for scheduling activities within the database. As with many things inside the database, not everything goes as planned, nor are we given adequate tools to fix some of the problems we encounter. With the eleven steps outlined here, hopefully you will have increased your arsenal to handle those run away jobs that have given the best of us a few tense moments.
