OracleBurleson Consulting has some good examples of complex job scheduling.

Of particular interest is this example which causes a job to be run only Monday through Friday:

-- ----------------------------------------------------------------
-- Submit job to begin at 0600 and run every hour, Monday - Friday
-- ----------------------------------------------------------------
dbms_job.submit(
:jobno,
'statspack.snap;',
trunc(sysdate+1)+6/24,
'trunc(
least(
next_day(SYSDATE - 23/24,''MONDAY''),
next_day(SYSDATE - 23/24,''TUESDAY''),
next_day(SYSDATE - 23/24,''WEDNESDAY''),
next_day(SYSDATE - 23/24,''THURSDAY''),
next_day(SYSDATE - 23/24,''FRIDAY'')
)
,''HH'')',
TRUE,
:instno);
commit;
end;
/

If you’re not familiar with Burleson Consulting’s site dba-oracle.com you probably should be. They have some great articles on Oracle administration. They even have linked to Life After Coffee recently on this same topic.

If you’re looking for more general information on the DBMS_JOB package, check out my article Scheduling Jobs using Oracle’s Job Queue.

database, database administration, database development, oracle

OracleOracle has taken an interesting step, and I hope others will follow.

Blogs.Oracle.com seems to have been created as an Oracle blogroll! Why is this important? Well, for one thing it means Oracle is encouraging their employees to blog, but additionally they are also linking to non-employee blogs.

Here’s the introduction from Blogs.Oracle.com:

Welcome to the Oracle blogging community, where Oracle executives, employees, and non-employees alike exchange views about best practices for using Oracle and industry-standard technologies. This continuous feedback loop helps Oracle stay in touch with the needs of the overall community, so keep those comments coming!

This not only validates what others have been saying regarding the value of blogging in the corporate environment, but also recognizes the role that non-employees play in the big picture.

Check it out at Blogs.Oracle.com. Right now there are a couple dozen employee blogs and fourty-something non-employee blogs. Hopefully they’ll stay on top of adding new folks as they come along.

blogging, information technology, internet, technology, web, web 2.0, web office, blog, database, database administration, database programming, dba, pl/sql, plsql, sql, oracle

OracleHere’s a way to force an Oracle date calculation to find a specific time of day every time. This is useful when using the DBMS_JOB package for job scheduling jobs as it gives you full control over the time of day a job runs.

The Oracle date data type is accurate down to the second, but if you want to ignore this precision and get to a certain time you can use the TRUNC function to truncate the date down to just the day, then add as many hours or minutes as you want.

By default the TRUNC command will truncate a date and time to midnight on the start of that date, so to consistently get, for example, 2:00 pm, we can truncate the time and add 14 hours.

SQL> SELECT to_char(trunc(sysdate) + 14/24, 'MM/DD/YYYY HH:MI AM') FROM dual;

TO_CHAR(TRUNC(SYSDA
-------------------
02/13/2006 02:00 PM

Here we truncate to midnight and add 14/24 of a day (14 hours). This works on the idea that the default increment for math with oracle dates is 1 day, so if we add 14/24 days we’re adding 14 hours. You could also use the NUMTODSINTERVAL function as described here, but it makes the command longer.

Applying this to scheduled jobs

When scheduling jobs with the DBMS_JOB package you specify the interval with a string which evaluates to an Oracle date. The interval is evaluated every time a job runs to calculate the next time the job should run. Typically the interval is something like 'SYSDATE + 1' if you want the job to run once a day.

The interval is evaluated each time the job starts. If the job runs at 11:00 PM, the interval would evaluate to 11:00 pm the following day; however, if you force the job to run with the DBMS_JOB.RUN at 2:30 pm, the interval would be evaluated to 2:30 pm the next day and you have changed your job to run at 2:30 every day thereafter.

To make sure things always run at the same time we can apply the method above to the interval when we submit the job.

SQL> BEGIN
DBMS_JOB.CHANGE(JOB => 21, WHAT => NULL, NEXT_DATE => NULL, INTERVAL => 'TRUNC(SYSDATE + 1) + 23/24';
END;
/

This would update job number 21 to use the string 'TRUNC(SYSDATE + 1) + 23/24' to calculate the next time this job should run. In this case, we take SYSDATE, plus 1 day, truncate it to midnight of that day, and add 23 hours. The end result is, no matter what time the job is run the interval will evaluate to 11:00 pm the next day.

oracle, database, dba, database programming, database administration, sql, plsql, pl/sql

Every once in a while I revisit the question of why I blog. Not because I doubt that I should, but because I see by blog as a living, evolving part of my personal and professional self.

Rod Boothby of Innovation Creators sums it up nicely in reference to MBA students:

With 10 minutes of effort a day, they use blogs (which are web pages that are easy to edit) to reach a massive audience. They can develop a worldwide reputation as an expert in their field. These MBAs don’t blog about parties or their dog. They blog business topics like marketing or financial derivatives. Even with traffic of only 5 to 10 people a day, that quickly translates into over 1,000 people who know who they are, and respect their knowledge and opinions.

This is from Rod’s whitepaper “The Next Wave in Productivity Tools – Web Office White Paper” in which he discusses how many Web2.0 technologies and the folks who use them are entering the corporate world. It’s well worth the read.

Thanks to John for sending this on to me.

blog, blogging, web, web 2.0, internet, information technology, technology, web office

My sister Carla points out an interesting detail in a survey she was solicited to take recently…

Please note that this study uses the Behavioral Lab’s new Inquisit system, which requires you to download a special applet. We regret that at this time, Inquisit studies only work on PCs running Internet Explorer. If you have not already downloaded this applet, you will be asked to do so when you link to the survey.

Now I’m not sure what this survey was for (and Carla didn’t mention on her LiveJournal) but we’ll assume it was targeted at only internet-savvy computer users, but wait, you also have to be on a PC, running Internet Explorer, and you need to be willing, able and patient enough to download their applet?

Hold it a minute… If I wanted to solicit bored geeks with no security concerns I’d just go phishing…

Who is responsible for the scientific integrity of this survey? Would you believe Stanford University?

survey, technology, computer, internet, browser, internet explorer, firefox

« Previous PageNext Page »