Instead of DBMS_LOCK.SLEEP Procedure Use SYS.DBMS_BACKUP_RESTORE.SLEEP For Time Interval > 3600 seconds (Doc ID 471246.1)

Last updated on JULY 05, 2017

Applies to:

PL/SQL - Version 8.1.6.0 and later
Information in this document applies to any platform.

Goal

DBMS_LOCK.SLEEP is very unstable and it does not provide the right sleep interval when sleep duration is more than 3600 seconds. Executing the following code block results in a slep time of ~ 50 minutes in opposite to the expected 120 minutes.

Example

connect system/manager

set serveroutput on

begin
  dbms_output.put_line('before sleep: '||to_char(sysdate,'HH:MI:SS'));
  dbms_lock.sleep(7200);
  dbms_output.put_line('after sleep : '||to_char(sysdate,'HH:MI:SS'));
end;
/  



Output:

connected.

before sleep: 10:01:41
after sleep : 10:50:07 

PL/SQL procedure successfully completed.

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms