Author's name: Jonathan Lewis
Author's Email: firstname.lastname@example.org
Date written: 24th July 2001
Oracle version(s): 7.3 - 22.214.171.124
From time to time, you may want a task to operate in a timed loop, perhaps waiting a few seconds before attempting to repeat an action that has failed, or possibly waiting until a specific point in time to start running. What methods does Oracle supply to make this possible. Note, however, that a couple of bugs, introduced some time around version 8, may cause problems.
There are two PL/SQL packged procedures that exist to allow a process to suspend itself temporarily withouth consuming CPU. These are dbms_lock.sleep() and user_lock.sleep() which is simply a layer that superimpsed over dbms_lock.sleep().
The dbms_lock package is created as part of the standard installation when catproc.sql is run. If you system does not have it installed, it can be created by running the script $ORACLE_HOME/rdbms/admin/dbmslock.sql, after connecting to the database under the SYS account. There is a public synonym in place for the package, but under 8.1 execute rights are granted to the role EXECUTE_CATALOG_ROLE, so you may not be able to call the function under a low-privilege account.
The user_lock package is not installed by default and has to be created by SYS by running the script $ORACLE_HOME/rdbms/admin/userlock.sql. This package ends up with a public synonym with execute rights granted to the role PUBLIC.
There is one significant difference between the two packages - dbms_lock.sleep() specifies the sleep time in seconds, user_lock sleep() specifies its sleep time in hundredths of seconds (or as the package code puts it: tens of milliseconds). The granularity, or precision, in both cases is 1/100 sec - the following calls both have the same effect from SQL*Plus:
The two functions do not consume CPU, so it is perfectly reasonable to use them to kick off a process at fairly regular intervals (but do also look at the dbms_job package for a more traditional approach to scheduling Oracle tasks) without wasting resources:
-- do some complicated pl/sql code here
-- then wait one minute before doing it again
An alternative use is to kick of a task at a specific time, by using a future-time calculation. Again you should consider using dbms_job for this task, but in some cases, the degree of accuracy that you can achieve through dbms_job.sleep() may be of value. For example, to start a process at 13:15 - rather than the first time after 13:15 that a job queue process (i.e. snp0 or one of its peers) wakes up:
86400 * (
) - sysdate
-- do something at 13:15 - or straight away if 13:15 has already passed
Note that dbms_lock.sleep() and user_lock.sleep() do not wait if the input parameter is negative.
My thanks to Jurij Modic for pointing out the following problems with the dbms_lock package in Oracle 8.1 (and possibly earlier versions). First, the timer overruns by 2.4% (plus or minus the odd centisecond due to CPU scheduling effects). This has been recorded as bug 1522119 - which is not visible on Metalink. Personally I attribute it to a numeric conversion issue - someone in Oracle Corp. has assumed that there are 1,024 milliseconds in a second ;)
The second bug is perhaps more serious - there is an overflow error around 2,097 seconds (or, on some platforms which use an unsigned word, 4,194 seconds) which makes all attempts to set a longer time-out go seriously wrong (Bug 1842189 applies). Jurij Modic sent me the following sample of times to demonstrate the issue.
Be warned - dbms_lock() at the latest release is predictably inaccurate up to 2,100 seconds, and thereafter unusable. There is no fix recorded as yet for either of these bugs. (Anyone who can test on 9.0 is invited to report their results)
One option for dealing with the problem of long sleep times is to break the sleep down into units that are short enough to bypass the problem. Jared Still has supplied the following example of a procedure to do this. Note that the procedure also takes care of the 2.4% error reported above.:
create or replace procedure accusleep ( seconds_in number )
v_chunk_size constant integer := 100;
-- v_compensation constant number := 0.976;
-- Modification to compensation factor suggested by Jeffe Legge May 2003
v_compensation constant number := 1000/1024;
v_seconds := seconds_in * v_compensation;
v_chunks := trunc(v_seconds/v_chunk_size);
v_remainder := mod(v_seconds, v_chunk_size);
for i in 1..v_chunks
And a demonstration of its use.
-- seconds from now til 10:00 AM
select (to_date(trunc(sysdate)+(10/24)) - sysdate ) * ( 24*60*60) into
seconds from dual;
Jeff Legge has suggested that for higher precision the code supplied by Jared Still could use an exact conversion factor, as shown above, and offers the following (unix based) test to show that the bug is still present in 9.2
.jeffl@mycroft:~/SQL$ time sqlplus
-S system/****** << _EOF_
> exec dbms_lock.sleep(1000);
With the result:
PL/SQL procedure successfully completed.
On 13th Jan, Chris Poole dropped me an email to say that he had found an alternative solution to the buggy DBMS_LOCK.SLEEP documented in this FAQ. The answer is posted on his website in the and involves a package that, funnily enough, he first found out about from the Co-operative Oracle Users’ FAQ site.
Further reading: N/A