Instead of DBMS_LOCK.SLEEP Procedure Use SYS.DBMS_BACKUP_RESTORE.SLEEP For Time Interval > 3600 seconds
Last updated on JULY 05, 2017
Applies to:PL/SQL - Version 220.127.116.11 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.
Sign In with your My Oracle Support account
Don't have a My Oracle Support account? Click to get started
Million Knowledge Articles and hundreds of Community platforms