Sleep function in ORACLE

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 useb

connect usera/abc123;

create or replace function usera.f_sleep( in_time number ) return number is
begin
 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 NUMBER
BEGIN
    DBMS_SESSION.sleep(i);
    RETURN i;
END;
SELECT my_sleep(3) FROM dual;

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

IN_TIME INT; --num seconds
v_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 date
LOOP
  EXIT WHEN v_now + (IN_TIME * (1/86400)) <= SYSDATE;
END LOOP;