Oracle – for when it was like that when you got there

Main menu

Post navigation

Help – DBMS_SCHEDULER keeps Spamming me…and can’t tell the time either

Sundays – a day of rest. Certainly true for me. Sunday morning is a time for lazing around leafing through the colour supplements and thinking about nothing in particular. Sunday 23rd October was a little bit different.
Wide-awake at 8 am ( I didn’t know that there was such a time as 8am on a Sunday), like several million others, I was wondering what would confront the All Blacks – the Gallic flair with which France had swept aside England or the Gallic shrug with which they had surrendered to Tonga ?
Look, I’m not really a New Zealander. Yes, I was born in Auckland but both my parents are English and I’ve lived most of my life in England. However, like anyone with a connection to the Land of the Long White Cloud, there is a part of my soul, however small, that takes the form of a Rugby ball.
At the end of the match, I was able to join my “fellow” Kiwis in, not so much paroxysms of joy as a huge collective sigh of relief.

On the whole though, I’d rather not have to see Sunday morning from that early on. So, if there is, for example, something that needs to run on my database on a Sunday morning, I’d rather the database just did it without my intervention.

What I plan to do here is :

set up a scheduler job

explore the ways in which we can control whether a class of job runs on a given database

stop jobs running on database startup

teach the scheduler how to tell the time – especially in terms of daylight saving

Creating a scheduler job

There is a really good and comprehensive guide on the myriad options available for Oracle’s database scheduler over at Oracle Base.

Here, I’m going to keep things quick and simple.
I’ve got a table that looks something like this :

CREATE TABLE reminders(
message VARCHAR2(4000),
msg_ts TIMESTAMP)
/

For some reason I can’t think of right now, I need to insert a row into this table at 8.30 every Sunday morning.
Rather than me having to drag my weary bones all the way to the keyboard, I can simply get the database to remember to do it. So, as a user with CREATE JOB privilege :

Notice anything odd about the START_TIME parameter ? Neither did I…at first. We’ll come back to this a little later. In the meantime, if we want to test that our job will actually do what we need it to, we can do a test run :

Hmmm, the start time doesn’t seem to have an offset to account for daylight savings. So, rather than using SYSDATE as the START_TIME when defining a job, maybe we should be using SYSTIMESTAMP ?
Well, yes and no. SYSTIMESTAMP will work fine for jobs that have run intervals of less than 24 hours. However, for jobs such as this one, it will blithely ignore changes in daylight saving. So, when, in my case, we move from the optimistically named British Summer Time ( BST) back to GMT, the job will run an hour later than before.

If we’re going to get the job to run at the correct time, we need to be explicit.
Just in case you’ve been typing along whilst reading this post and are now busy cursing me as you’ll have to go back and drop and re-create any jobs you’ve already set up, this script should set things right :

Help, my database is spamming me

So, you have your scheduled jobs running happily in the database. You probably have a few sending e-mails to you as the DBA to let you know all is well, or if there is a problem.
What happens when you refresh your test database with a copy of Live ?

Yep, as soon as you open the instance, the scheduler checks to see if any jobs should have run. If the current system time is later than the next_run_time, then they’ll kick off immediately. Even if the first thing you do after opening the database is to disable these jobs, your inbox is still going to be flooded with spam.

In the good old days of dbms_job, you could avoid this simply by setting the job_queue_processes parameter to 0. This solution has now finally found it’s way into 11gR2 as well. However, if you’re currently stuck somewhere between 10g and 11gR1 then you’ll need an alternative.
The workaround below is a quick n dirty version of this infinitely more elegant solution proposed by Dan Morgan.

Getting classy

The first thing we need to do is create a service. For our specific purposes, you can think of a service as….well…a thingy.

If the service is not running, then none of the jobs associated with the class that is, in turn, associated with the service, will run.
So how do we stop the spam ? Dan’s solution used a simple config file based on the same format as oratab. Mine is a bit more basic, but does have the advantage of meaning that you have one less thing to remember when cloning your database.
Here, we’re going to use a database startup trigger :

Simple really, only start the service if we’re running on production. When I start a test database (in this case, where the database name is not XE, the trigger will not enable the services ( and therefore the jobs) thus keeping my inbox spam free.
Regular readers may be wondering how I got through this entire explanation without any Monty Python references.

Take it easy, I’m just recovering

What about those times when you’re bringing the production database back up after some unforeseen downtime. The last thing you want is a load of potentially long-running jobs to kick off and hog all of the available database resources.
If you haven’t used the Services method above, you’re going to have to find an alternative solution…

STARTUP NOMOUNT
ALTER SYSTEM ENABLE RESTRICTED SESSION;

Once you open the database, restricted session is still enabled and the jobs don’t run.
You can then disable them at your leisure before…

ALTER SYSTEM DISABLE RESTRICTED SESSION;

Once you open the database, restricted session is still enabled and the jobs don’t run.
You can then disable them at your leisure before…

ALTER SYSTEM DISABLE RESTRICTED SESSION;

Deb has been sulking most of the way through this post. In fact, she’s not been entirely happy ever since the French knocked out Wales in the Semis. Oh well, only another 4 years till the next World Cup. I mean, how long can a sulk last ?