dbms_session-sleep-18c

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.
* [46]DBMS_SESSION.SLEEP
* [47]DBMS_LOCK.SLEEP

Related articles.
* [48]DBMS_SESSION : Managing Sessions From a Connection Pool in
Oracle Databases

DBMS_SESSION.SLEEP

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.
SET SERVEROUTPUT ON
BEGIN
DBMS_OUTPUT.put_line('Time 1: ' || TO_CHAR(SYSTIMESTAMP, 'HH24:MI:SS.FF'));

-- Pause for 1 second.
DBMS_SESSION.sleep(1);

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

-- Pause for half a second.
DBMS_SESSION.sleep(0.5);

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

PL/SQL procedure successfully completed.

SQL>

DBMS_LOCK.SLEEP

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.
--------------------------
Source .... : https://oracle-base.com/articles/18c/dbms_session-sleep-18c
--------------------------