Database News You Can Use
A monthly newsletter for Oracle usersJune 2004
From Database Specialists
Expert onsite and remote DBA services
for your Oracle databases
Tel: 415-344-0500 • Toll-Free: 1-888-648-0500
Ask DB Specialists: Managing Scheduled Jobs
You’re invited to submit your Oracle-related questions to us at email@example.com. Include your name, telephone number and problem description. If your question is answered in The Specialist next month, you will receive a free copy of our Oracle Wait Events reference poster. (US shipping addresses only). Linux Technology Center
This month’s question comes from Carmen in the western US: How does Oracle handle scheduled jobs when the total length of time needed to execute the job exceeds the job interval? In other words, if a dbms_job is supposed to execute every five minutes and an iteration takes eight minutes to complete, is there any possibility that you could have two instances of the job running simultaneously?
Terry Sutton of the Database Specialists team responds: The dbms_job facility is good at ensuring a job doesn’t conflict with itself. You can observe its behavior by looking at the dba_jobs view. The key columns in the view for our purposes are THIS_DATE, NEXT_DATE, and INTERVAL. While a job is running, NEXT_DATE doesn’t change; it holds the value it held when the job started. This is what prevents the job from being started again before the current execution of it is finished.
Though your interval is five minutes, NEXT_DATE won’t change for the entire eight minutes it takes to complete the job. If INTERVAL represents less time than it takes to complete the job, when the job is finished NEXT_DATE is set to the time the job finished. This means that the job will be started again as soon as one of the job queue processes checks for jobs ready to run (controlled by the job_queue_interval instance parameter in Oracle 8i and earlier, non-adjustable in Oracle 9i). This means that the job could start again almost immediately.
If the INTERVAL represents more time than it takes to complete the job (e.g., a job scheduled to run every five minutes which takes three minutes to complete) then, when the job is completed, NEXT_DATE will be set to the start time of the job (THIS_DATE) plus the time represented by INTERVAL (e.g., five minutes after the job started). NEXT_DATE is not based on when the job completed unless the INTERVAL time is shorter than the time it took to complete the job.
Oracle 10g still has the dbms_job facility, but it also introduces the more flexible and powerful Scheduler. We don’t have room here to go into all the new features of the Scheduler, but they include:
We discussed Oracle on Linux last month, and it remains a popular topic. Our Oracle on Linux installation guides continue to soar in popularity, so don’t miss our white paper on Installing and Configuring Oracle 9i on Linux. Dedicated Linux fans will also want to keep an eye out on the Oracle Technology Network’s Linux Technology Center. There’s plenty of information relating to Oracle on Linux. Here’s a sampling of recent articles: Oracle Database Migrations
You also may be interested to note that LinuxWorld will be held in San Francisco from August 2-5, 2004.
In almost every DBA’s life, there comes a time when you have to migrate a system from one database vendor to another. The Art of Programming Network has compiled quite a few resources on this topic and made them available on their website. Here are some highlights: DBA Click Resource
Here’s yet another place to click your way to more Oracle resources. DBAClick.com offers Oracle scripts, installation guides, manuals, tips, white papers, books, tools reviews, news and links. There’s also a FAQ section, Oracle DBA Forums and instructions on how DBAs can upload and share their own favorite scripts and resources on the site. Some of the categories on the site include:
You’ll find it all at http://www.dbaclick.com.