Instead of DBMS_LOCK.SLEEP Procedure Use SYS.DBMS_BACKUP_RESTORE.SLEEP For Time Interval > 3600 seconds
(Doc ID 471246.1)
Last updated on FEBRUARY 22, 2019
Applies to:PL/SQL - Version 188.8.131.52 and later
Information in this document applies to any platform.
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.
set serveroutput on
dbms_output.put_line('before sleep: '||to_char(sysdate,'HH:MI:SS'));
dbms_output.put_line('after sleep : '||to_char(sysdate,'HH:MI:SS'));
before sleep: 10:01:41
after sleep : 10:50:07 PL/SQL procedure successfully completed.
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!
In this Document