DBMS_LOCK - To Set Time Delay
During data management activities, ETL, etc. time lag may be needed for synchronization of systems, external dependencies or for other reasons. Using the Oracle package/procedure DBMS_LOCK.sleep(time in seconds) lag can be created. The DBMS_LOCK.sleep(time) can be also used to execute certain procedure, script etc. in specific time intervals. Example below shows creation of two second time lag. 

DECLARE 
 l_start PLS_INTEGER;
 l_end   PLS_INTEGER;

BEGIN
  l_start := dbms_utility.get_time; 
  dbms_output.put_line('Start Time ['||TO_CHAR(SYSDATE,'hh24:mi:ss')||'] -> ' ||TO_CHAR(l_start));
  dbms_lock.sleep(2); 
  l_end := dbms_utility.get_time; 
  dbms_output.put_line('End Time   ['||TO_CHAR(SYSDATE,'hh24:mi:ss')||'] -> ' ||TO_CHAR(l_end));
  dbms_output.put_line('Time Lapsed -> ' ||TO_CHAR(l_end-l_start));
END;
/

ORACLE DBMS_LOCK()


Oracle registered trademark of Oracle Corporation.
Last Revised on: April 14, 2014

  74669