Scheduling Jobs with DBMS_SCHEDULER

1.0 Introduction
We’ve had the ability to schedule work in the database for a long time... think DBMS_JOB, for example (which, I hasten to add, is still there in 10g, so old code doesn’t break). New in Oracle 10g, however, is DBMS_SCHEDULER, a more powerful, robust and flexible work scheduling engine... and a lot trickier to get sorted in your head, if you’re anything like me! Honestly: if Oracle had deliberately set out to make the topic of scheduling something to happen every ten minutes as obscure, complicated and scary as possible, they couldn’t have done a better job! That’s only my opinion, of course. But then I’m writing the article!
To be fair, the complications are related to Scheduler’s power and subtlety: the comparison that springs to mind is between, say, designing a website in Microsoft Frontpage and doing it in Macromedia DreamWeaver. I know which is easier, but I also know which can achieve the better results long-term.
So I’ve chosen to break the discussion of Scheduler into two parts. Part 1 sets out to treat Scheduler as the Frontpage of job scheduling programs: nothing too complicated; just the basic stuff needed to achieve the scheduling of a job. Part II then has a look at the hard stuff: creating schedules, scheduling windows, window priorities and so on. With any luck, Part II won’t look anything like as scary by the time you get to it, because you’ll be entirely at home with the basics!
2.0 Privileges and Preparations
There are two primary interfaces to the Job Scheduler in 10g. If you like doing things at the command line, you’ll love DBMS_SCHEDULER. If you are more a GUI kind of person, then you’ll need to get familiar with the Enterprise Manager Database Control web management utility. I’ll be writing about Enterprise Manager in another article soon, so this one is going to concentrate on doing things with the keyboard -though I’ll throw in an EM screenshot or two here as well.
Before we begin, therefore, I will need to make sure that I have a database user account set up that can make use of the Scheduler -and that means it the rights to create jobs. I’ll make sure I start with a clean slate, and an account with only the minimum privileges needed for things to work effectively, like so:
SQL> drop user scott cascade;
User dropped.

SQL> @?/rdbms/admin/utlsampl

C:\WINNT\system32>sqlplus / as sysdba

SQL*Plus: Release 10.1.0.2.0 - Production on Tue Feb 15 05:33:54 2005
Copyright (c) 1982, 2004, Oracle. All rights reserved.

SQL> grant create job, create procedure to scott;
Grant succeeded.
The create job system privilege gives a user execute rights on the dbms_scheduler package, and hence the ability to create and schedule jobs in his own schema. If I’d wanted Scott to be able to create jobs on behalf of, and in the schema of, some other user then I could have granted him the create any job system privilege. The grant of the create procedure system privilege is there simply so that in the rest of this article I can show Scott creating assorted stored procedures, the automatic execution of which can then be scheduled. It is not, in other words, a privilege intrinsically needed to be able to schedule work on the database.
With the above privileges granted, I can now log on as Scott, and create the following procedure:
SQL> connect scott/tiger
Connected.

SQL> create or replace procedure new_emp
   2   is
   3 begin
   4   insert into emp (empno,ename, job, mgr, hiredate, sal, comm, deptno)
   5   values ((select max(empno)+1 from emp),
   6   'HJR','CLERK',7698,sysdate,1000,null,30);
   7   commit;
   8 end;
   9 /

Procedure created.
It’s not a particularly brilliant piece of code: it simply inserts a new employee called ‘HJR’ (feel free to change that to your own initials!) into the ever-familiar EMP table. Most of the column information for the new record is hard-coded to specific values which are going to be of no interest to us. But you might notice that the HIREDATE column is set to the current system time. Inspecting that column, in other words, will show us precisely what clock time it was when the new record was entered.
Apart from the grant of those privileges and the creation of this little procedure, that’s all the preparation you’ll need. So enjoy yourself as we go for a tour around the new 10g Job Scheduler.{mospagebreak title=Creating a Job}
3.0 Creating a Job
At its absolute simplest, creating a scheduled job in 10g requires that you supply just three pieces of information:
·    A name for the job
·    A job type (which I’ll describe in just a moment) and
·    An action for the job to perform (ditto)


Commonsense additionally suggests you might like to tell Oracle when the job should start and how frequently it should be repeated thereafter.
Job names can be pretty much anything you want, provided it doesn’t match anything else in the database. That is, a job can’t be called the same name as a table or a constraint, for example.
There are just three Job types: PLSQL_BLOCK, STORED_PROCEDURE and EXECUTABLE. You use PLSQL_BLOCK when you want to type in, directly to DBMS_SCHEDULER, the PL/SQL code you want to run. In other words, it lets you type in an anonymous PL/SQL block. STORED_PROCEDURE is what you specify when there is an existing stored procedure, package or function you wish the scheduler to run for you. And EXECUTABLE is what you’d supply, as the name might suggest, when you want the scheduler to run some O/S program or script -for example, a shell script or a Windows Scripting Host script.
The Job action is simply the name of the stored procedure to run if you’d said the job type was STORED_PROCEDURE, or the name of the O/S script or executable you want to run if you’d said the job type was EXECUTABLE. If you’d asked to schedule a PLSQL_BLOCK, then the job action is actually the PL/SQL code itself.
Apart from that lot, as I mentioned earlier, you will probably want to supply a start time (and that uses the TIMESTAMP WITH TIME ZONE data type) and a repeat interval. The repeat interval can be tricky, as I’ll show you in just a moment, but if it makes you feel more comfortable then you can also use traditional date/time mathematics to specify one: so, for example, an interval of SYSDATE+(1/24) means a job will be re-run every 1 hour.
One of the other new features in Oracle 10g, however, is a slew of new calendaring expressions. They can take a bit of getting used to, as with anything unfamiliar, but they are actually easier to use than the old SYSDATE+Fraction technique! These expressions usually take two components: a frequency and then an interval. For example, a job might need to be run with a frequency of HOURLY and an INTERVAL of 1 -and that would be the same as scheduling an interval of SYSDATE+(1/24). As another example, you might want the job run ‘FREQ=MINUTELY; INTERVAL=25’. That is the actual syntax you use, and in this case specifies a repeat interval of ‘every 25 minutes’: the ‘minutely’ means ‘using minutes’ not ‘I am very, very small’!
It can get much more complicated than that, of course. How about this:
‘FREQ=WEEKLY;BYWEEKNO=6,12,18,24,30,36,42,48;BYDAY WED,FRI;BYHOUR=21;BYMINUTE=15’
Scary, huh?! It simply means: ‘At 9:15PM on Wednesday and Friday of every sixth week, starting with week number 6’. Try doing all of that using SYSDATE+something mathematics! Now you know why such expressions were invented for 10g: they make subtle and complex job schedules a (relative) piece of cake! The complete list of frequencies you can specify are: yearly, monthly, weekly, daily, hourly, minutely and secondly. Likewise, the list of interval types include BYMONTH, BYMONTHDAY, BYYEARDAY and BYSECOND (not including the ones I’ve already shown you in the command above). Some of those are distinctly silly, if you ask me: BYSECOND implies performing a job at an exact number of seconds within the minute... but you’ll recall that Oracle makes no guarantees at all about whether a job will ever get performed precisely on schedule, so trying to be that precise is just wishful thinking!
Incidentally, if you don’t want to specify a repeat interval at all, then that’s fair enough: it simply means you are asking for a job to be executed once and once only, and there’s nothing wrong with that at all.
Anyway: with those five pieces of information to hand, I’m ready to create my first scheduler job, and the command to do so would look something like this:
SQL> exec dbms_scheduler.create_job -
> (job_name=>'HIRE', -
> job_type=>'STORED_PROCEDURE',-
> job_action=>'NEW_EMP', -
> start_date=>SYSTIMESTAMP, -
> repeat_interval=>'FREQ=MINUTELY; INTERVAL=5', -
> end_date=>SYSTIMESTAMP+31, -
> enabled=>TRUE)

PL/SQL procedure successfully completed.
I’ve broken the command down into separate lines, using the ‘-’ continuation character between them, so that it’s hopefully not quite the mess it can look when you type it all on one line! Taking the syntax apart bit by bit, we see:
Job Name:
I’ve just invented one here that happens to be called HIRE. It could have been almost anything, though, so long as it didn’t duplicate the name of any other object in the database
Job Type:
I’ve told the scheduler to run a stored procedure.
Job Action:
And here I tell it which stored procedure to run: in my case, the NEW_EMP procedure I created at the start of this article.
Start Date:
I cheated on this one. I could have typed in a full-blown TIMESTAMP WITH TIME ZONE string to indicate a future start time, but instead I simply used the in-built SYSTIMESTAMP function, which means the job is to start “right now”. In fact, the repeat interval will have to elapse before the very first run of the job, but that’s close enough.
Repeat Interval:
Hopefully you recognise this now! I’m asking for the job to be re-executed every five minutes. I realise it makes no sense to employ a new member of staff every five minutes, but it will serve as an example!
End Date:
You don’t actually need to specify an end date: if you miss it out, the job just keeps repeating for ever. On the grounds that I don’t want my EMP table accidentally to acquire a bazillion new records, however, I’ve insisted the job stop running 31 days from now. Again, I’ve used the SYSTIMESTAMP function to ensure the value is returned to DBMS_SCHEDULER with the right data type.
Enabled:
Rather bizarrely, to my way of thinking at least, the default behaviour of the Scheduler is to create jobs in a disabled state. You would have to manually enable the job (execute dbms_scheduler.enable(‘job-name’) if you wanted the job to be actually carried out. Since I definitely want my little PL/SQL procedure to be run every five minutes, I’ve taken the opportunity here to insist that the job be run right from the word go.
At this point, I suggest you go and make yourself a cup of tea and relax for a while. If I’ve got it right, the HIRE job should run itself every 5 minutes, so by the time the tea has brewed, and the biscuits nibbled, the EMP table should have a fair few more employees in it than it did to start with...
Right... now you’ve stopped filling your face with cream cakes and calories, let’s look at what’s happened to the EMP table...
SQL> select empno, ename, job, mgr,
  2  to_char(hiredate,'HH-MI-SS')
  3  from emp where ename='HJR';

     EMPNO ENAME      JOB              MGR TO_CHAR(
---------- ---------- --------- ---------- --------
     7935  HJR        CLERK           7698 08-20-31
     7936  HJR        CLERK           7698 08-25-31
     7937  HJR        CLERK           7698 08-30-31
     7938  HJR        CLERK           7698 08-35-31
     7939  HJR        CLERK           7698 08-40-31
     7940  HJR        CLERK           7698 08-45-31
     7941  HJR        CLERK           7698 08-50-31
     7942  HJR        CLERK           7698 08-55-31
     7943  HJR        CLERK           7698 09-00-31
     7944  HJR        CLERK           7698 09-05-31

10 rows selected.
Unless I’m very much mistaken, that looks suspiciously like a new employee has been hired every five minutes! So the job creation and scheduling process has worked -and it wasn’t particularly difficult at all, was it?
I should perhaps mention that these results, whilst genuine, are quite remarkable in one sense: it is extraordinary how the scheduler has activated the job every five minutes to the second. That’s because I’m running on a test rig, and there’s nothing much else happening on my database, but in a production setting, it’s most unlikely you’d get this level of spot-on precision. In fact, Oracle makes no guarantees about the execution of scheduled jobs at all: the relevant processes will get around to performing the required work when they are free to do so. If they’re tied up, busy performing the other 4000 jobs that have been scheduled, then you’ll just have to wait your turn.{mospagebreak title=Checking it has worked}
4.0 Checking it has worked
The example I’m using in this article, of course, provides its own check that the job has actually been performed successfully and on time -but that’s not likely to be true for most of your jobs in the real world. You need some way of validating when a job ran, and whether it ran successfully to completion or hit some sort of snag. Enterprise Manager’s Database Control will provide a nice GUI-ish way of doing that, of course, but if you’re stuck in good old SQL*Plus, then the new view USER_SCHEDULER_JOB_RUN_DETAILS is your friend, even if its name definitely isn’t! There are equivalent DBA_ and ALL_ views as well, naturally. Here’s a snippet from mine:
SQL> column log_date format a45
SQL> column job_name format a10

SQL> select log_date, job_name, cpu_used, status
  2  from user_scheduler_job_run_details
  3  where job_name='HIRE';

LOG_DATE                                      JOB_NAME     CPU_USED STATUS
--------------------------------------------- ---------- ---------- -------------
15-DEC-04 08.20.31.296000 AM +11:00           HIRE                0 SUCCEEDED
15-DEC-04 08.25.31.328000 AM +11:00           HIRE                0 SUCCEEDED
15-DEC-04 08.30.31.406000 AM +11:00           HIRE                0 SUCCEEDED
15-DEC-04 08.35.31.375000 AM +11:00           HIRE                0 SUCCEEDED
15-DEC-04 08.40.31.421000 AM +11:00           HIRE                0 SUCCEEDED
15-DEC-04 08.45.31.359000 AM +11:00           HIRE                0 SUCCEEDED
15-DEC-04 08.50.31.406000 AM +11:00           HIRE                0 SUCCEEDED
15-DEC-04 08.55.31.375000 AM +11:00           HIRE                0 SUCCEEDED
15-DEC-04 09.00.31.421000 AM +11:00           HIRE                0 SUCCEEDED
I suppose the critical column in this report is the last one: I can see at a glance my job ran to a successful completion every time it was executed. The alternative STATUS, of course, would be FAILURE. I’ve shown the CPU_USED column here not because it is actually of much use to me in this trivial test environment, but because it’s nice to know that you can readily assess the impact which jobs have on the resources left available for the rest of the database. In a production setting, you’d have more realistic results here and it would be important to keep an eye on it: too much CPU spent on executing jobs means not enough CPU left for other users, after all. There are also other columns in the view, which I haven’t shown here, that can tell you, for example, the difference between the time a job was supposed to start and when it actually started. There’s also a RUN_DURATION column, so you don’t have to do any clock arithmetic to work out how long it takes for a job to finish.
5.0 How does it work?
When you create a job, it is entered as a new row in a database-wide scheduled jobs table. You can view the contents of that table by selecting from the new USER_SCHEDULER_JOBS view (and there is of course an equivalent DBA_ view, too):
SQL> column job_name format a10
SQL> column job_creator format a10
SQL> column job_action format a10
SQL> column start_date format a40
SQL> column repeat_interval format a30

SQL> select job_name, job_creator, job_type, job_action,start_date
   2 from user_scheduler_jobs;

JOB_NAME   JOB_CREATO JOB_TYPE         JOB_ACTION START_DATE             ---------- ---------- ---------------- ---------- ----------------------------------------
HIRE       SCOTT      STORED_PROCEDURE NEW_EMP    15-FEB-05 09.33.07.500000 AM +11:00
As you can see, the table pretty much stores word-for-word what you originally submitted when creating the job. Bear in mind that there is an older view available in a 10g database: USER_JOBS:
SQL> select * from user_jobs;
no rows selected
...and as this short query tells you, the new 10g Scheduler has absolutely nothing to do with that older view. That’s there for people who continue to use DBMS_JOB as their scheduling mechanism, but the point is that they are two completely different mechanisms, and they have nothing to do with each other. So the views relating to one will not show any details concerning the other. And if I haven’t already made it clear, DBMS_JOB is there in 10g for backwards compatibility only: you are strongly urged to make the move to Scheduler and, to be quite frank, you’d be mad not to in any case. Scheduler, after all, is heaps more flexible and capable than tired old DBMS_JOB.
As for how jobs get performed... Well, you’ll need to have a look at the various background processes to work that out:
SQL> connect / as sysdba
Connected.

SQL> select distinct program from v$process;

PROGRAM
--------------------------------------------
ORACLE.EXE (CJQ0)
ORACLE.EXE (CKPT)
ORACLE.EXE (DBW0)
ORACLE.EXE (J000)
ORACLE.EXE (J001)
ORACLE.EXE (LGWR)
ORACLE.EXE (MMAN)
ORACLE.EXE (MMNL)
ORACLE.EXE (MMON)
ORACLE.EXE (PMON)
ORACLE.EXE (QMNC)
ORACLE.EXE (RECO)
ORACLE.EXE (SHAD)
ORACLE.EXE (SMON)
ORACLE.EXE (q000)
PSEUDO

16 rows selected.
This is an example from a 10g database running on Windows, of course, and I’ve therefore had to use the V$PROCESS view (as SYS) to see the assorted background processes. If you were running on Linux or Unix, you could simply grep for your ORACLE_SID:
[oracle@garnet oracle]$ echo $ORACLE_SID
lx10
[oracle@garnet oracle]$ ps -ef | grep lx10
oracle 2125 1 0 09:26 ? 00:00:00 ora_pmon_lx10
oracle 2127 1 0 09:26 ? 00:00:00 ora_mman_lx10
oracle 2129 1 0 09:26 ? 00:00:00 ora_dbw0_lx10
oracle 2131 1 0 09:26 ? 00:00:00 ora_lgwr_lx10
oracle 2133 1 0 09:26 ? 00:00:00 ora_ckpt_lx10
oracle 2135 1 0 09:26 ? 00:00:00 ora_smon_lx10
oracle 2137 1 0 09:26 ? 00:00:00 ora_reco_lx10
oracle 2139 1 0 09:26 ? 00:00:00 ora_cjq0_lx10
oracle 2145 1 0 09:26 ? 00:00:00 ora_qmnc_lx10
oracle 2152 1 0 09:26 ? 00:00:00 ora_mmon_lx10
oracle 2154 1 0 09:26 ? 00:00:00 ora_mmnl_lx10
oracle 2191 1 0 09:32 ? 00:00:00 ora_q000_lx10
oracle 2201 2083 0 09:33 pts/0 00:00:00 grep lx10
In either case, you can see a process there with a name of, or including, CJQ0. That is the Co-ordinator of the Job Queue process -and as you can tell from the zero in his name, there can be more than one of them if the database is really busy. It is this process which scans the database-wide scheduled jobs table, and spawns slave processes to perform any jobs found there that are due to be performed.
There is nothing to configure about the Co-ordinator: you don’t get to specify how many of them there are, nor how often or frequently it/they should check the scheduled jobs table. It is all entirely automatic. Similarly, the job queue slaves are not configurable. They certainly don’t depend on the setting of the JOB_QUEUE_PROCESSES initialisation parameter, for example.{mospagebreak title=Managing Jobs}
6.0 Managing Jobs
Once a job has been created, you’ll maybe want to suspend it, stop it, resume it and/or delete it. At least, I hope you will, because that’s what I’ll cover next!
6.1 Suspending/Resuming a Job
I’ve already touched on the fact that Jobs are created, by default, in a disabled state. I then went on to create my job in a fully-enabled state from the start, but that gives you the clue as to how you can suspend and resume a job: it simply comes down to knowing how to enable or disable a job after it has been created, and that involves using just two new procedures within the DBMS_SCHEDULER package: ENABLE and (oddly enough!) DISABLE:
SQL> show user
USER is "SCOTT"

SQL> exec dbms_scheduler.enable('HIRE')
PL/SQL procedure successfully completed.

SQL> exec dbms_scheduler.disable('HIRE')
PL/SQL procedure successfully completed.
The procedures simply take one argument: the name of the job to be enabled or disabled. Nothing too complex there, then!
One minor complication could arise if a job is currently running at the very moment you seek to disable it. If that is true, then the disable command I’ve shown here will fail with an error. However, you can force the issue by adding one extra parameter to the command:
SQL> exec dbms_scheduler.disable('HIRE',TRUE)
PL/SQL procedure successfully completed.
That second parameter is the force one, and is either true or false (and by default, it’s false). If you set it to true, then the disabling of the job will definitely occur without error -although the currently-running instance of the job will still be allowed to run to completion.
Once a job has been suspended in this way, it will stay that way until you change its status to enabled once more. That means no further runs of that job will take place until you give the go-ahead.
6.2 Stopping a Job
If you don’t want to semi-permanently prevent a job from running (by disabling it), you might nevertheless want to interrupt one, specific running instance of a job. That requires you to use the STOP_JOB procedure of the DBMS_SCHEDULER package (and yes, in passing, I find it incredibly irritating that some of the procedures are one-word ones -such as ENABLE or DISABLE- and some are two-word ones -such as STOP_JOB. There’s a reason for that, as we’ll see in Part II):
SQL> exec dbms_scheduler.stop_job('HIRE')
BEGIN dbms_scheduler.stop_job('HIRE'); END;

*
ERROR at line 1:
ORA-27366: job "SCOTT.HIRE" is not running
ORA-06512: at "SYS.DBMS_ISCHED", line 162
ORA-06512: at "SYS.DBMS_SCHEDULER", line 398
ORA-06512: at line 1
As you can see -quite reasonably- you get an error if you try stopping a job that isn’t actually running at that moment. Had the job been running, however, then the job slaves would have gracefully halted their work. That could take some time, and in any case the job processes might not be able to halt the execution of the job -it rather depends on the nature of the job itself, of course- and in that case the command will return yet another (though different) error.
If you’ve tried gracefully stopping a job, but received that ‘no can do’ error, then you’ll want to be a bit more forceful about things, and that requires that you supply a new argument to the procedure:
SQL> exec dbms_scheduler.stop_job('HIRE',TRUE)
BEGIN dbms_scheduler.stop_job('HIRE',TRUE); END;

*
ERROR at line 1:
ORA-27486: insufficient privileges
ORA-06512: at "SYS.DBMS_ISCHED", line 162
ORA-06512: at "SYS.DBMS_SCHEDULER", line 398
ORA-06512: at line 1
Well, this is how you’d do it: once again, there’s a FORCE argument to be supplied, and once again it takes a value of TRUE or FALSE, with FALSE being the ‘graceful stop’ default. As you can see, though, my attempt to specify that FORCE should be used to stop the job fails miserably with an ‘insufficient privileges’ error message. That’s because merely being able to create a job, as Scott can, doesn’t give you the right to create merry havoc on the database by aborting a job in mid-stream. For that, you’ll need the MANAGE SCHEDULER system privilege, which I’ll discuss in more detail in Part II.
6.3 Deleting a Job
If you want to completely obliterate a job from the system, rather than just interrupting or suspending it, then you’ll need the to use the DROP_JOB procedure:
SQL> exec dbms_scheduler.drop_job('HIRE')
PL/SQL procedure successfully completed.
As you might have expected by now, it takes the name of the job as its sole argument. The entry in the scheduled jobs table (and hence in the USER_SCHEDULER_JOBS view) is removed completely, and it is as though the job had never been created. And, as you also might have expected by now, there is an optional FORCE parameter which can be TRUE or FALSE (the default is FALSE). The issue is whether you should be allowed to delete/drop a job when that job is currently running. If you don’t force the matter, then a running job cannot be dropped and the command will generate an error; specify TRUE, however, and Oracle will stop the job for you and then proceed with the job deletion.
7.0 Conclusion
That’s about all I want to say about Scheduler in this article. Hopefully, you can see it’s not a monster: in fact, it’s quite easy to use for simple scheduling, provided you can work out those calendaring expressions for the repeat intervals (and they’re not too difficult after a bit of practice). Even in this simplest of descriptions, though, I’ve hinted at capabilities that the old DBMS_JOB package never had -such as the ability to run an executable that is stored outside the database. We used to have to use cron or at to run those sorts of things... and then often wished we hadn’t, because the O/S scheduler would run the executable when the database happened to be down! With Scheduler, the executables can still be run, but it’s the database that’s doing the running. So if the database is down, no worries: the executable simply won’t be executed.
Scheduler is a nice touch, therefore. It’s not one of those all-singing, all-dancing bits of functionality you wonder how we ever managed without. But it’s welcome nonetheless. Certainly recommended (and certainly recommended that you stop using DBMS_JOB).