Using DBMS_LOCK has some disadvantages. DBMS_LOCK is a powerful package. It’s not granted by default and some DBAs don’t like grant this package, at all. They have good reason. If you decide to use DBMS_LOCK package to implement sleeping, you should use a proxy package and grant this proxy package instead of direct use of DBMS_LOCK package:

You should be also aware of Bug 1842189 (Link requires access to Metalink): DBMS_LOCK.SLEEP does not provide the right sleep interval when sleep duration is more than 3600 seconds. According Metalink this bug is reproducing from 8.1.6 through 11.1.0.6.

However, you should test it out on your environment, if you require such long sleeping intervals.

A valid workaround is to use DBMS_BACKUP_RESTORE package. Procedure DBMS_BACKUP_RESTORE.SLEEP gives the expected amount of sleep even if the time interval is more than 3600 seconds. But SYS.DBMS_BACKUP_RESTORE is also very powerful (you should use proxy package) and it is undocumented, so probably for internal use only (RMAN uses it).

I like approach using java.lang.Thread.sleep method mapped by PL/SQL procedure. But you have to have Java installed on your database to implement this.

CREATE OR REPLACE PROCEDURE sleep(x_millis IN NUMBER) AS LANGUAGE JAVA
NAME 'java.lang.Thread.sleep(int)';
/
SET TIMING ON
EXECUTE sleep(10000);

You should by also aware of some difficulties with killing sleeping sessions. A sleeping session is an active session. When you issue an ALTER SYSTEM KILL SESSION command on this session, it is not killed until it awakes from sleep. On Unix-like operating systems, you can kill corresponding OS process with kill command. On Windows, you can kill corresponding thread of oracle process with orakill command.

I will demonstrate this on a simple example. In the first session, I will run this script, which will sleep for 100 seconds:

Immediately, in the second session, I will try to kill the first sleeping session with the ALTER SYSTEM command. This command will wait up to 60 seconds for the sleeping session to be terminated, but then I will receive a message indicating that the sleeping session has been only marked to be terminated and this session will continue to sleep. After 100 seconds, when it awakes, it will be terminated.

Comte, see the point 4. at the beginning of this post 😉

Mustafa ÖZBEKsays:

Thank u.

Pavol Babelsays:

The trouble with killing / canceling of sleep could be related to more possible scenarios:

1) Oracle was using only in-band break checking for server processes somewhere before 10gR2. That means during execution of long running job (which could i.e. sleep for 3600 seconds 🙂 ), oracle was forced to poll socket inside the code. And there were many bugs connected to dynamic sql or dbms_lock.sleep where oracle simply forgot to poll socket. Not sure with version, but this was still case for 10gR2. Even the OCICancel (when pressing CTRL+C) was not working, the server process first finished whole sleep interval and then finished. Let’s see every 9 seconds poll inside pdswait kernel function:

2) Oracle uses now out-ouf bound checing (OOB). Even when oracle server process on DB server is busy, the client sends an URG TCP/IP packet, which is handled by OS TCP/IP stack, which is able to call an interrupt for server process.
There were few bugs connected to OOB in the past. However the thing to worry is, that most of modern firewalls (Cisco, Palo Alto…) clear URG packets by default (the simply set the URG flag inside packet to 0, since in RFC it is not recommended to use URG, due some historical bad implementations in operating systems and possible vulnerability).

When OOB kicks in, no socket polling will be seen:

The trouble with killing / canceling of sleep could be related to more possible scenarios:

1) Oracle was using only in-band break checking for server processes somewhere before 10gR2. That means during execution of long running job (which could i.e. sleep for 3600 seconds 🙂 ), oracle was forced to poll socket inside the code. And there were many bugs connected to dynamic sql or dbms_lock.sleep where oracle simply forgot to poll socket. Not sure with version, but this was still case for 10gR2. Even the OCICancel (when pressing CTRL+C) was not working, the server process first finished whole sleep interval and then finished. Let's see every 9 seconds poll inside pdswait kernel function:

System altered.
Elapsed: 00:00:07.02

So from 11gR2 onwards (at least) it should be sufficient to use “alter system kill session” to kill session inside dbms_lock.sleep even when client connected from Windows (but could take so few “additional” seconds when compared to linux/unix client ).