8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 18c » Here

DBMS_SESSION.SLEEP Replaces DBMS_LOCK.SLEEP in Oracle Database 18c

The SLEEP procedure is added to the DBMS_SESSION package and deprecated from the DBMS_LOCK package in Oracle Database 18c.

Related articles.


The SLEEP procedure is added to the DBMS_SESSION package, so it is available to all sessions with no additional grants needed and no dependency on the DBMS_LOCK package.

The procedure suspends the session for the specified number of seconds. The seconds parameter can accept any valid number, including fractions of a second.

  sys.DBMS_OUTPUT.put_line('Time 1: ' || TO_CHAR(SYSTIMESTAMP, 'HH24:MI:SS.FF'));

  -- Pause for 1 second.

  sys.DBMS_OUTPUT.put_line('Time 2: ' || TO_CHAR(SYSTIMESTAMP, 'HH24:MI:SS.FF'));

  -- Pause for half a second.

  sys.DBMS_OUTPUT.put_line('Time 3: ' || TO_CHAR(SYSTIMESTAMP, 'HH24:MI:SS.FF'));
Time 1: 19:52:52.957473000
Time 2: 19:52:53.957187000
Time 3: 19:52:54.457213000

PL/SQL procedure successfully completed.



The Oracle 18c documentation no longer lists the SLEEP procedure in the DBMS_LOCK package, but it is still present for backwards compatibility. It is now deprecated, so you should transition your code to use the DBMS_SESSION package instead, and revoke any grants to the DBMS_LOCK package where they were only granted to give access to the SLEEP procedure.

Why was this necessary? The DBMS_LOCK package was not accessible by default, so an explicit grant was necessary for a session to use it. By giving access to this package, you also give the grantee the ability to execute the other subroutines in the DBMS_LOCK package, which affords them quite an excessive level of functionality just for the ability to suspend their session for a few seconds.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.