We need sleep function many a times in our code, may be for application logic or even sometimes for monitoring purpose. We always had this available with sleep() function that resides within DBMS_LOCK ==> DBMS_LOCK.SLEEP

This was always a big security risk as granting access to DBMS_LOCK will make a way to get access to other functions/procedures within which is not always necessary.

So from 18C, Oracle comes with SLEEP function within a publicly granted package ==> DBMS_SESSION.SLEEP.

DBMS_SESSION.SLEEP

From 18C DBMS_LOCK.SLEEP is deprecated, but it is still present for backwards compatibility.

I would suggest, whoever is planning to upgrade their databases to 19C in near future, should upgrade their PL/SQL codes to use DBMS_SESSION.SLEEP instead of DBMS_LOCK.SLEEP. Also one should make sure to revoke any grants to the DBMS_LOCK package where they were intended to give access to only SLEEP procedure.

References:

ER 23557076 : PUBLIC SLEEP FUNCTION

Hope u will find this post very useful. 🙂

Cheers

Regards,

Adityanath