There are few approaches how to implement sleeping in PL/SQL:
- DBMS_LOCK.SLEEP procedure (precision: hundredths of a second)
- DBMS_BACKUP_RESTORE.SLEEP (precision: seconds)
- java.lang.Thread.sleep method mapped by PL/SQL procedure (precision: milliseconds)
- Autonomous transaction (In fact, this Tanel’s post inspired me to write this) (precision: seconds)
Of course, I belive there are some other aproaches.
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:
CONNECT / AS SYSDBA CREATE USER sleep IDENTIFIED BY sleep ACCOUNT LOCK; GRANT EXECUTE ON dbms_lock TO sleep; CREATE OR REPLACE PACKAGE sleep.dbms_lock IS PROCEDURE sleep (seconds IN NUMBER); END dbms_lock; / CREATE OR REPLACE PACKAGE BODY sleep.dbms_lock IS PROCEDURE sleep (seconds IN NUMBER) IS BEGIN sys.dbms_lock.sleep(seconds); -- do NOT forget sys prefix to avoid recursion! END sleep; END dbms_lock; / CREATE USER test IDENTIFIED BY test; GRANT CREATE SESSION TO test; GRANT EXECUTE ON sleep.dbms_lock TO test; GRANT CREATE SYNONYM TO test; CONNECT test/test CREATE OR REPLACE SYNONYM dbms_lock FOR sleep.dbms_lock; SET TIMING ON EXECUTE dbms_lock.sleep(1);
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 184.108.40.206.
Here is a test case:
CONNECT / AS sysdba BEGIN dbms_output.put_line('before sleep: '||to_char(SYSDATE,'DD.MM.YYYY HH24:MI:SS')); dbms_lock.sleep(6000); dbms_output.put_line('after sleep : '||to_char(SYSDATE,'DD.MM.YYYY HH24:MI:SS')); END; /
I tested it on various versions, but I encounter this bug only on 220.127.116.11.0:
18.104.22.168.0 on Red Hat 9 (28 minutes and 25 seconds elapsed)
before sleep: 04.10.2008 22:29:35 after sleep : 04.10.2008 22:58:00 PL/SQL procedure successfully completed.
10.2.0.1.0 on CentOS (100 minutes elapsed – bug didn’t appear)
before sleep: 05.10.2008 20:17:08 after sleep : 05.10.2008 21:57:08 PL/SQL procedure successfully completed.
10.2.0.4.0 on CentOS (100 minutes elapsed – bug didn’t appear)
before sleep: 04.10.2008 22:58:00 after sleep : 05.10.2008 00:38:00 PL/SQL procedure successfully completed.
22.214.171.124.0 on CentOS (100 minutes elapsed – bug didn’t appear):
before sleep: 04.10.2008 22:24:53 after sleep : 05.10.2008 00:04:53 PL/SQL procedure successfully completed.
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:
select to_char(sysdate,'hh24:mi:ss') start_time from dual; set timing on begin dbms_application_info.set_module('sleep', 'sleep'); sleep(x_millis => 100000); end; /
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.
select to_char(sysdate, 'hh24:mi:ss') start_time from dual; set linesize 120 column sid new_value sid column serial new_value serial column status format a8 select to_char(s.sid) sid, to_char(s.serial#) serial, s.status, p.spid from v$session s, v$process p where s.action = 'sleep' and s.STATUS <> 'KILLED' and s.paddr = p.addr(+); set timing on alter system kill session '&sid, &serial' immediate; set timing off select s.sid, s.serial#, s.status, p.spid from v$session s, v$process p where s.action = 'sleep' and s.paddr = p.addr(+);
Output of the first session:
START_TI -------- 15:24:29 PL/SQL procedure successfully completed. Elapsed: 00:01:40.84
We can see that it takes 100 seconds, even we tried to kill it.
Output of the second session:
START_TI -------- 15:24:34 SID SERIAL STATUS SPID ---------------------------------------- ---------------------------------------- -------- ------------ 48 2782 ACTIVE 11267 old 1: alter system kill session '&sid, &serial' immediate new 1: alter system kill session '48, 2782' immediate alter system kill session '48, 2782' immediate * ERROR at line 1: ORA-00031: session marked for kill Elapsed: 00:01:01.80 SID SERIAL# STATUS SPID ---------- ---------- -------- ------------ 48 2782 KILLED 11267
Note: The value in the SPID column could be used for the kill or the orakill command to kill the sleeping session on the OS level.