Is there a way to suspend a task, or make it sleep, for a period of time ?

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:

executedbms_lock.sleep(0.05);

executeuser_lock.sleep(5);

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:

begin

loop

-- do some complicated pl/sql code here

-- then wait one minute before doing it again

dbms_lock.sleep(60);

end loop;

end;

/

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:

declare

m_count_down number;

begin

select

86400 * (

to_date(

to_char(trunc(sysdate),'yyyymmdd')||'1315',

'yyyymmddhh24mi'

) - sysdate

)

intom_count_down

from dual;

dbms_lock.sleep(m_count_down);

--do something at 13:15 - or straight away if 13:15 has already passed

end;

/

Note that dbms_lock.sleep() and user_lock.sleep() do not
wait if the input parameter is negative.

BUG Warning:

My thanks to JurijModic 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). JurijModic
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)

Update 2nd Nov 2002 from Jared
Still

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 )

is

v_chunk_size constant integer := 100;

--v_compensation constant number := 0.976;

--

--Modification to compensation factor suggested by JeffeLegge May 2003

Update 9th Aug 2003 from Jeff Legge

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

Update 24th Jan 2005 from Chris Poole

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 tips section
and involves a package that, funnily enough, he first found out about from the
Co-operative Oracle Users’ FAQ site.