Scheduling Jobs Using Oracle’s Job Queue

OracleOracle database offers a job queue for scheduling certain operations to happen routinely in a database. The functionality is similar to UNIX cron jobs with the primary difference being if your database is not running, the job will not attempt to run.

Scheduling is accomplished through the DBMS_JOB package which is provided by Oracle. While nearly anything can be scheduled, my rule of thumb is to use this only for things which happen exclusively within the database. For tasks which involve manipulating files at the operating system level I still prefer cron.

Oracle will start a coordinator job queue (CJQ0) process to handle scheduled jobs. The CJQ0 process will keep track of the schedule and start helper processes (J000 – J999) to execute the scheduled jbos.

This is being written for use with Oracle Database 9i, but will likely work in most of the recent revisions. As always, consult the documentation for your release before attempting any of this.

Setting the database up for job execution:

Before we can schedule job execution we need to make sure the database is set up to process jobs.

The first thing we need to do is check the number of job queue processes available to execute jobs. For that we check the job_queue_processes initialization parameter.

SQL> show parameter job_queue_processes

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 100

This parameter should be set higher than the maximum number of simultaneous jobs you expect to run (the limit is 1000). Some jobs may require more than one process, so headroom is important.

If this parameter is set to 0 jobs will not be processed. If you wish to start processing jobs the parameter can be set dynamically with the ALTER SYSTEM command.

ALTER SYSTEM SET job_queue_processes = 100;

This command enables job queue processing by starting the CJQ0 process. Similarly, you can disable all job queue processing and stop the CJQ0 process by setting the parameter to 0.

ALTER SYSTEM SET job_queue_processes = 0;

Changes to the parameter with the ALTER SYSTEM command will only be in effect until the database is restarted. Make sure you change them in your init or spfile if you want the changes to be permanent.

Submitting a job to the queue

The procedure DBMS_JOB.SUBMIT is called to submit a new job to the queue. The procedure is called in the following format:

BEGIN
dbms_job.submit(JOB => :jobnumber, -- (this is a return variable, not a supplied number)
WHAT => 'code to execute',
NEXT_DATE => first_execution_date,
INTERVAL => next_execution_date);
commit;
END;
/

An example should make things a little more clear:

VARIABLE jobnumber number
BEGIN
DBMS_JOB.SUBMIT(JOB => :jobnumber,
WHAT => 'DBMS_STATS.GATHER_DATABASE_STATS(options => ''GATHER AUTO'');',
NEXT_DATE => to_date('11:30 01/23/06','HH24:MI MM/DD/YY'),
INTERVAL => 'SYSDATE + 1');
COMMIT;
END;
/
print jobnumber

JOBNUMBER
----------
21

Here we see a complete job submission. First we set up a variable to hold the job number which will be assigned automatically and returned by the procedure. We then begin a PL/SQL block and call the DBMS_JOB.SUBMIT command.

The JOB parameter will be the variable which will be populated with the job number. If you need to alter or delete a job in the future it will be easiest to find by job number.

The WHAT parameter is the code to be executed. This could be a simple statement or (as in our example) a call to another procedure or function. Note that the parameters for the procedure we’re calling must be in two single quotes so the single quotes are interpreted correctly.

The NEXT_DATE parameter specifies the first time this job should be run. This can be any valid Oracle date. Here we are telling it to run January 23 at 11:30 am.

If NEXT_DATE is not in the future you may run into problems, so it may be better to use a formula for NEXT_DATE. Anything that evaluates to a valid Oracle date is fair game.

Finally we specify INTERVAL, the way to calculate how often we want the job to run at. This is a date string which will be evaluated each time the job is run. Here SYSDATE + 1 will be evaluated to once a day. SYSDATE + .5 would cause the job to execute every 12 hours, or SYSDATE + 7 would cause it to run once a week. Any formula can be used here as long as it evaluates to a date.

The best way to assure a job will run at a specific time every day is to truncate the date and add a number of hours to it. I have explained this in greater detail in a separate article Getting a Specific Time of Day in Oracle.

If NULL is specified instead of an interval the job will be run once at the specified first execution time then be removed from the job queue.

The commit here is important to assure the newly created job will run. We then end the PL/SQL block and execute it. For future reference we print the jobnumber variable.

Now this job will be executed at 11:30am on 1/23/06 and every 24 hours after that.

Viewing the job queue

Oracle provides four useful views of the job queue.

DBA_JOBS lists information on all the jobs in the database.

ALL_JOBS has the same information as DBA_JOBS but only on jobs which are accessible tot he current user.

USER_JOBS again has the same job information, but will only list jobs owned by the current user.

DBA_JOBS_RUNNING contains information on all jobs currently running in the database. This view can be joined with the other views for detailed information on running jobs.

Running a job manually

While the point is to automate jobs, occasionally you may find you need to run a job manually. To do this you can call the RUN procedure. This is especially useful if you need to run a job which has been marked as broken.

BEGIN
DBMS_JOB.RUN(JOB => 21);
END;
/

Note: if you use DBMS_JOB.RUN to manually execute a job the value for NEXT_DATE will be updated based on the current date and time and your formula. This becomes important if you have a job running at, say 11:00 pm with an interval of SYSDATE + 1, but then you run it manually at 3:45pm, the next_date will be recalculated at that time and it will now be run at 3:45pm each day.

How to tell if a job has failed

When a database job fails an error will be written to the Oracle alert log with the error number of ORA-12012 and will include the job number which failed. Jobs could fail for a variety of reasons but most common are missing or altered objects or insufficient privileges to execute.

If a job fails to execute after 16 attempts Oracle will mark it as broken and stop attempting to run it. This will be indicated by a ‘Y’ in the BROKEN column of the dba_jobs view. If you want to tell Oracle to not run a job you can manually mark it as broken by executing the following:

BEGIN
DBMS_JOB.BROKEN(JOB => 21, BROKEN => TRUE);
END;
/

The job will remain broken and will not be run until you either force it to run or mark it as not broken. When marking it as not broken you must also specify the next date for the job to run. The following will mark job 21 as not broken and have it execute at 11:00pm on January 23.

BEGIN
DBMS_JOB.BROKEN(JOB => 21, BROKEN => FALSE, NEXT_DATE => TO_DATE('23:00 01/23/06', 'HH24:MI MM/DD/YY'));
END;
/

Changing a job

You can update a job with the DBMS_JOB.CHANGE procedure. This procedure takes job number, code, next date and interval, in that order, as conditions. Whatever you don’t want to change can be passed as NULL.

With that in mind, the command to change just the interval would look something like this:

BEGIN
DBMS_JOB.CHANGE(JOB => 21, WHAT => NULL, NEXT_DATE => NULL, INTERVAL => 'SYSDATE + 7');
END;
/

This would change job number 21 to execute every 7 days. The previous value is discarded.

Removing a job

To remove a job from the job queue you will need the job number. If you’re not sure what it is you should be able to find it by querying one of the views listed above.

Once you have the job number, run the following command.

BEGIN
DBMS_JOB.REMOVE(JOB => 21);
END;
/

This will remove the job and it will not be executed again. You can only remove jobs that you own. If this is run while the job is executing it will not be interrupted, but will not be run again.

oracle, database, database administration, database development

SQL – Finding the Last Weekday in a Month

OracleSidney V. commented on my post Performing Math on Oracle Dates that he was looking for a way to calculate the last weekday of a month. After some research I concluded that Oracle does not provide a function for this, so I wrote one.

I started with the last_day Oracle function to find the last day of the month. I’m using December 2005 as an example as the last day of the month was a Saturday.

SELECT last_day(to_date('12/2005','MM/YYYY')) FROM dual;

LAST_DAY(
---------
31-DEC-05

This gives us the last day of the month regardless of the day of the week. Now we rewind from that 7 days by subtracting 7 from the date so we can look at the last week in detail.

SELECT last_day(to_date('12/2005','MM/YYYY')) - 7 FROM dual;

LAST_DAY(
---------
24-DEC-05

This lets us start 7 days before the end of the month. Now we can look at this last week in detail. To see the last Monday of the month we use the next_day function to go forward from the last_day() - 7.

SELECT
next_day((last_day(to_date('12/2005','MM/YYYY')) - 7), 'Monday') AS day
FROM dual;

DAY
---------
26-DEC-05

Using this we could find the last Monday, Tuesday, etc. of the month. In this case we want to find the last weekday (Monday through Friday) of the month. We can accomplish this with a UNION of several of these statements.

SELECT day
FROM (SELECT next_day((last_day(to_date('12/2005','MM/YYYY')) - 7), 'Monday') AS day FROM dual)
UNION (SELECT next_day((last_day(to_date('12/2005','MM/YYYY')) - 7), 'Tuesday') AS day FROM dual)
UNION (SELECT next_day((last_day(to_date('12/2005','MM/YYYY')) - 7), 'Wednesday') AS day FROM dual)
UNION (SELECT next_day((last_day(to_date('12/2005','MM/YYYY')) - 7), 'Thursday') AS day FROM dual)
UNION (SELECT next_day((last_day(to_date('12/2005','MM/YYYY')) - 7), 'Friday') AS day FROM dual);

DAY
---------
26-DEC-05
27-DEC-05
28-DEC-05
29-DEC-05
30-DEC-05

So we know when the last Monday, Tuesday, Wednesday, Thursday, and Friday of this month was, but which was latest? For that we’ll apply the SQL MAX function to the results of the above union. To do this we put the union above in the FROM part of the query.

SELECT MAX (day)
FROM ((SELECT next_day((last_day(to_date('12/2005','MM/YYYY')) - 7), 'Monday') AS day FROM dual)
UNION (SELECT next_day((last_day(to_date('12/2005','MM/YYYY')) - 7), 'Tuesday') AS day FROM dual)
UNION (SELECT next_day((last_day(to_date('12/2005','MM/YYYY')) - 7), 'Wednesday') AS day FROM dual)
UNION (SELECT next_day((last_day(to_date('12/2005','MM/YYYY')) - 7), 'Thursday') AS day FROM dual)
UNION (SELECT next_day((last_day(to_date('12/2005','MM/YYYY')) - 7), 'Friday') AS day FROM dual));

MAX(DAY)
---------
30-DEC-05

Now we have the date of the last weekday of the month. The Oracle to_char function can be applied to this result to get the date in a different format.

SELECT to_char(MAX (day), 'DAY, MM/DD')
FROM ((SELECT next_day((lASt_day(to_date('12/2005','MM/YYYY')) - 7), 'Monday') AS day FROM dual)
UNION (SELECT next_day((lASt_day(to_date('12/2005','MM/YYYY')) - 7), 'Tuesday') AS day FROM dual)
UNION (SELECT next_day((lASt_day(to_date('12/2005','MM/YYYY')) - 7), 'Wednesday') AS day FROM dual)
UNION (SELECT next_day((lASt_day(to_date('12/2005','MM/YYYY')) - 7), 'Thursday') AS day FROM dual)
UNION (SELECT next_day((lASt_day(to_date('12/2005','MM/YYYY')) - 7), 'Friday') AS day FROM dual))
;

TO_CHAR(MAX(DAY)
----------------
FRIDAY , 12/30

Now let’s try this logic on another date to make sure it’s sound. We’ll try today (Jan, 6 2006).

SELECT to_char(MAX (day), 'DAY, MM/DD')
FROM (
(SELECT next_day((last_day(sysdate) - 7), 'Monday') AS day FROM dual)
UNION (SELECT next_day((last_day(sysdate) - 7), 'Tuesday') AS day FROM dual)
UNION (SELECT next_day((last_day(sysdate) - 7), 'Wednesday') AS day FROM dual)
UNION (SELECT next_day((last_day(sysdate) - 7), 'Thursday') AS day FROM dual)
UNION (SELECT next_day((last_day(sysdate) - 7), 'Friday') AS day FROM dual)
)
;

TO_CHAR(MAX(DAY)
----------------
TUESDAY , 01/31

So the logic works, but this would be a lot of typing if you wanted to use it, so let’s make it into a function so we can use it anywhere in the database.

CREATE OR REPLACE FUNCTION last_weekday(month_check DATE DEFAULT sysdate)
RETURN date
IS
last_weekday_date DATE;
BEGIN
SELECT MAX(day) INTO last_weekday_date
FROM ((SELECT next_day((last_day(month_check) - 7), 'Monday') AS day FROM dual)
UNION (SELECT next_day((last_day(month_check) - 7), 'Tuesday') AS day FROM dual)
UNION (SELECT next_day((last_day(month_check) - 7), 'Wednesday') AS day FROM dual)
UNION (SELECT next_day((last_day(month_check) - 7), 'Thursday') AS day FROM dual)
UNION (SELECT next_day((last_day(month_check) - 7), 'Friday') AS day FROM dual));
RETURN last_weekday_date;
END;
/

Note: If you want other users to be able to use this function you have to grant them execute on it. Creating a synonym for it may also be usefull.

Now let's test drive the function we created. We assigned a default of sysdate so if we call it without any parameters it should return the last weekday of the current month.

SELECT last_weekday FROM dual;

LAST_WEEKDAY_DATE
---------
31-JAN-06

If we pass this function a date as a parameter it will return the last weekday of that month.

SELECT last_weekday(to_date('12/2005','MM/YYYY')) FROM dual;

LAST_WEEK
---------
30-DEC-05

Thanks Sidney for the challenge!

UPDATE: Oraboy writes with this algorithm. His code is much shorter than mine and likely more efficient. Thanks Oraboy!

With date_v as
(select last_day(to_date('&MM-&DD-&yyyyy','MM-DD-YYYY')) mydate from dual)
select max(date_v.mydate-(i-1)) last_weekday
from date_v,(select level i from dual connect by level < =3) where to_char(date_v.mydate-(i-1),'Dy') not in ('Sat','Sun')

oracle, sql, dba, database administration, database development

Oracle Optimal Flexible Architecture Explained

OracleI have posted about the Oracle OFA (Optimal Flexible Architecture) and recent changes to OFA for Oracle Database 10g, but I wanted to go into a more practical application of these rules to act as a quick reference.

This is not intended to be a complete explanation of the OFA standard. For more complete information refer to the OFA whitepaper. The OFA Standard Recommendations below are taken directly from the OFA whitepaper.

OFA recommendations 9-11 pertain to very specific installation types I have chosen to exclude them. The examples below are based on a UNIX environment.

OFA Standard Recommendation 1: Name all mount points that will hold site specific data to match the pattern /pm where p is a string constant chosen not to misrepresent the contents of any mount point, and m is a unique fixed-length key that distinguishes one mount point from another.

Typical Application: Name operating system mount points with the convention /u01, /u02, /u03 etc.

Options: Anything can be used instead of the leading ‘u’, however be careful not to use something which could eventually misrepresent the future contents of the directory.

OFA Standard Recommendation 2:Name home directories matching the pattern /pm/h/u, where pm is a mount point name, h is selected from a small set of standard directory names, and u is the name of the owner of the directory.

Typical Application: The original OFA whitepaper states the oracle user home directory be placed in a directory like /u01/app/oracle.

Options: This rule is typically overlooked. The oracle user is usually given a home directory which matches other UNIX users, such as /export/home/oracle/ or /home/oracle, however the directory /u01/app/oracle (or similar) should be created for software installation (see recommendation 4). The recommended home directory (/u01/app/oracle) is typically referred to as $ORACLE_BASE.

OFA Standard Recommendation 3:Refer to explicit path names only in files designed specifically to store them, such as the UNIX /etc/passwd file and the Oracle oratab file; refer to group memberships only in /etc/group.

Typical Application: Whenever possible, refer to the oracle user’s home directory as ~oracle and use other environment variables such as $ORACLE_HOME and $ORACLE_BASE instead of full paths. As an example, you would typically use $ORACLE_BASE/admin instead of /u01/app/oracle/admin.

Options: ~oracle, $ORACLE_BASE and $ORACLE_HOME are typically used as environment variables. Other variables such as $ORACLE_ADMIN can be set and used in a similar fashion.

OFA Standard Recommendation 4: Store each version of Oracle Server distribution software in a directory matching the pattern h/product/v, where h is the login home directory of the Oracle software owner, and v represents the version of the software.

Typical Application: Oracle server software should be stored in a directory below the home ($ORACLE_BASE) in the format $ORACLE_BASE/product/9.2. 9.2 should be replaced with the version of Oracle software installed.

Options: This could be on any of the /u01 sequence of partitions.

NOTE: For 10g Oracle has added another level to allow multiple installs of the same version of software. The new recommendation is to install software in a directory in the format of $ORACLE_BASE/product/10.1/db_1.

OFA Standard Recommendation 5:For each database with db_name=d, store database administration files in the following
subdirectories of h/admin/d:
• adhoc — ad hoc SQL scripts for a given database
• adump — audit trail trace files
• arch — archived redo log files
• bdump — background process trace files
• cdump — core dump files
• create — programs used to create the database
• exp – database export files
• logbook — files recording the status and history of the database
• pfile — instance parameter files
• udump — user SQL trace files
where h is the Oracle software owner’s login home directory.

Typical Application: Again the $ORACLE_BASE directory should be used for the home directory, resulting in a path of $ORACLE_BASE/admin/adump

Options: Not all these directories are always used; however, if there are multiple DBAs I highly recommend the adhoc and logbook directories.

OFA Standard Recommendation 6:Name Oracle database files using the following patterns:
• /pm/q/d/control.ctl — control files
• /pm/q/d/redon.log — redo log files
• /pm/q/d/tn.dbf — data files
where pm is a mount point name, q is a string denoting the separation of Oracle data from all other files, d is the db_name OFA Standard • 21 Oracle System Performance Group Technical Paper, September 24, 1995 of the database, n is a distinguishing key that is fixed-length for a given file type, and t is an Oracle tablespace name. Never store any file other than a control, redo log, or data file associated with database d in /pm/q/d.

Typical Application: Control, redo, and data files are typically stored in one or more of the /u01 series of partitions in a subdirectory called oradata then in a folder matching the database name. The resulting path should resemble /u01/oradata/orcl.

Only control, redo, and data files should reside in this path and they should have the appropriate extensions (.ctl, .log or .dbf respectively).

Control and redo files should have a fixed length number to identify them, such as control01.ctl or redo04.log.

Data files should contain the tablespace name and a fixed length number resulting in the format system01.dbf.

Options: Many place the database name in these files. This is redundant since the database name is already in the path to these files and should be avoided as it complicates changing the database name.

OFA Standard Recommendation 7: Separate groups of segments with different lifespans, I/O request demands, and backup frequencies among different tablespaces. For each Oracle database, create the following special tablespaces in addition to those needed for applications segments:
• SYSTEM — data dictionary segments only
• TEMP — temporary segments only
• RBS — rollback segments only
• TOOLS — general-purpose tools only
• USERS — miscellaneous user segments

Typical Application: Here a segment is a piece of data in the database associated with a table, index, or other database object. Basically you should represent the tablespaces listed above and any needed for the appropriate application. Do not put application data in any of these tablespaces, but instead create one or more tablespaces for each application.

Options: TOOLS and USERS tablespaces may be omitted, however it is likely you will want them in the long run.

NOTE: For database 10g Oracle has added a new tablespace called SYSAUX which contains all non-essential system components.

OFA Standard Recommendation 8: Name tablespaces connotatively with eight or fewer characters.

Typical Application: The eight character limit is not necessary; however, tablespace names should be kept fairly short. More importantly tablespace names should be indicative of their contents. WEBCT_DATA or BANNER_INDEX_SMALL are acceptable tablespace names, but DATA or INDEX would not be.

Options: There is a lot of flexibility here. Using a consistent convention at your site is key.

oracle, sql, dba, database administration, database development, database security, database, oracle security

Performing Math on Oracle Dates

Here are some quick and dirty examples for adding and subtracting days and months and finding the difference between dates in Oracle.

These examples select the result from the ‘dual’ table. Dual is a dummy table that exists in all Oracle databases. I am also using sysdate heavily. Sysdate simply returns the current date and time.

Adjusting Days, Weeks, Hours and Minutes

To add and subtract days from a date we simply use + or -. Here are some examples:

SQL> SELECT sysdate + 7 FROM dual;

SYSDATE+7
---------
06-DEC-05

SQL> SELECT sysdate - 30 FROM dual;

SYSDATE-3
---------
30-OCT-05

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

TO_CHAR(SYSDATE-14,
-------------------
11/15/2005 09:41 AM

In the first example we see that the query returns 7 days from today. The second one shows 30 days before today. In the third query I have added the to_char function so we see hour and minute. This shows us that while the date has changed, the time has not.

The first and third examples above also show how adding days would be used to add weeks. If you wanted to simplify this you could add the logic into the query like this:

SQL> SELECT sysdate + (7 * 2) FROM dual;

SYSDATE+(
---------
13-DEC-05

Likewise to work with hours you would use fractional days. The simplest way to show this is by dividing the hours by 24 right in the query. The parentheses may not be necessary, but I feel they make the query a little clearer.

Here I show the current time, then the current time plus two hours:

SQL> SELECT to_char(sysdate, 'HH:MI AM') FROM dual;

TO_CHAR(
--------
09:48 AM

SQL> SELECT to_char(sysdate + (2/24), 'HH:MI AM') FROM dual;

TO_CHAR(
--------
11:48 AM

An alternative to this method is to use the numtodsinterval function. The example above can instead be written like this:

SQL> SELECT
to_char(sysdate + numtodsinterval(2, 'HOUR'), 'HH:MI AM')
FROM dual;

TO_CHAR(
--------
11:57 AM

Here the numtodsinterval function is doing the work of dividing 2/24 for hours. Valid options for the numtodsinterval are ‘DAY’, ‘HOUR’, ‘MINUTE’, or ‘SECOND’. Here is an example using ‘MINUTE’. When working with minutes the numtodsinterval function is much more readable.

SQL> SELECT
to_char(sysdate + numtodsinterval(45, 'MINUTE'), 'HH:MI AM')
FROM dual;

TO_CHAR(
--------
10:47 AM

Adjusting Months and Years

To work with months and years (either of which may have a varying number of days) Oracle has provided the function numtoyminterval. This works much like the numtodsinterval function mentioned above by taking a number and a string. Valid options for the string are ‘YEAR’ or ‘MONTH’.

SQL> SELECT
to_char(sysdate + numtoyminterval(5, 'MONTH'), 'MM/DD/YYYY')
FROM dual;

TO_CHAR(SY
----------
04/29/2006

SQL> SELECT
to_char(sysdate + numtoyminterval(2, 'YEAR'), 'MM/DD/YYYY')
FROM dual;

TO_CHAR(SY
----------
11/29/2007

Comparing dates

Let’s say we want to compare some dates and find out how many days or weeks between them. To simply see the result in days we can use the minus operator like this:

SQL> SELECT
TO_DATE('12/25/2005', 'MM/DD/YYYY') - sysdate
FROM dual;

TO_DATE('12/25/2005','MM/DD/YYYY')-SYSDATE
------------------------------------------
26.5124421

The result is expressed in days including fractional hours. Of course if we wanted weeks we could just divide by 7. Similarly if we’re looking for hours we could multiply by 24, but if we want months we need to use the months_between function.

SQL> SELECT
months_between(sysdate, to_date('01/01/2006', 'MM/DD/YYYY'))
FROM dual;

MONTHS_BETWEEN(SYSDATE,TO_DATE('01/01/2006','MM/DD/YYYY'))
----------------------------------------------------------
-1.0829409

Finding the Latest or Earliest Date in a Set

The Oracle functions greatest and least can be used on dates to return the latest or earliest date.

SQL> SELECT
greatest(sysdate, to_date('09/11/2005','MM/DD/YYYY'),
to_date('12/25/2005','MM/DD/YYYY'))
FROM dual;

GREATEST(
---------
25-DEC-05

SQL> SELECT
least(sysdate, to_date('09/11/2005','MM/DD/YYYY'),
to_date('12/25/2005','MM/DD/YYYY'))
FROM dual;

LEAST(SYS
---------
11-SEP-05

What’s the Last Day in a Given Month

The last_day function can be used to return the last day in a given month.

SQL> select last_day(sysdate) from dual;

LAST_DAY(
---------
30-NOV-05

Next Day of the Week After a Date

The next_day function gives the date of the next occurrence of a day of the week (‘Monday’, ‘Tuesday’, etc.) after a given date. Here we see the date of the next Sunday after today:

SQL> select next_day(sysdate,'Sunday') from dual;

NEXT_DAY(
---------
04-DEC-05

Still haven’t found what you’re looking for?

You may also want to look at these other articles:

Converting Time Zones in Oracle
Oracle, SQL, Dates and Timestamps
UNIX timestamp to Oracle Date Conversion

oracle, sql, dba, database administration, database development