Sleep function in ORACLE

Short of granting access to DBMS_LOCK.sleep, this will work but it's a horrible hack:

IN_TIME INT; --num secondsv_now DATE;-- 1) Get the date & time SELECT SYSDATE   INTO v_now  FROM DUAL;-- 2) Loop until the original timestamp plus the amount of seconds <= current dateLOOP  EXIT WHEN v_now + (IN_TIME * (1/86400)) <= SYSDATE;END LOOP;

Create a procedure which just does your lock and install it into a different user, who is "trusted" with dbms_lock ( USERA ), grant USERA access to dbms_lock.

Then just grant USERB access to this function. They then wont need to be able to access DBMS_LOCK

( make sure you don't have usera and userb in your system before running this )

Connect as a user with grant privs for dbms_lock, and can create users

drop user usera cascade;drop user userb cascade;create user usera default tablespace users identified by abc123;grant create session to usera;grant resource to usera;grant execute on dbms_lock to usera;create user userb default tablespace users identified by abc123;grant create session to userb;grant resource to usebconnect usera/abc123;create or replace function usera.f_sleep( in_time number ) return number isbegin dbms_lock.sleep(in_time); return 1;end;/grant execute on usera.f_sleep to userb;connect userb/abc123;/* About to sleep as userb */select usera.f_sleep(5) from dual;/* Finished sleeping as userb *//* Attempt to access dbms_lock as userb.. Should fail */begin  dbms_lock.sleep(5);end;//* Finished */

From Oracle 18c you could use DBMS_SESSION.SLEEP procedure:

This procedure suspends the session for a specified period of time.

DBMS_SESSION.SLEEP (seconds  IN NUMBER)

DBMS_SESSION.sleep is available to all sessions with no additional grants needed.Please note that DBMS_LOCK.sleep is deprecated.

If you need simple query sleep you could use WITH FUNCTION:

WITH FUNCTION my_sleep(i NUMBER)RETURN NUMBERBEGIN    DBMS_SESSION.sleep(i);    RETURN i;END;SELECT my_sleep(3) FROM dual;