The Jonathan Lewis Pages

FAQ - How can I make a session suspend itself temporarily

Question:

I want a session to suspend itself for a while - how can I do this.

Answer:

There are two PL/SQL pacakged procedures that exist to allow this to happen. 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 using the script $ORACLE_HOME/rdbms/admin/dbmslock.sql, which should be run 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 granularty in both cases is 1/100 sec - the following calls both have the same effect:

	dbms_lock.sleep(0.05);
	user_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 into dbms_job) 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, byt 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 process wakes up:

declare
	m_count_down number;
begin
	select 
		86400 * (
			to_date(
				to_char(trunc(sysdate),'yyyymmdd')||'1315',
				'yyyymmddhh24mi'
			) - sysdate
		)
	into m_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.

Back to FAQ Index