8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | 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.

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 down to hundredths of a second.

set serveroutput on
begin
  sys.dbms_output.put_line('Time 1: ' || to_char(systimestamp, 'hh24:mi:ss.ff'));

  -- Pause for 1 second.
  sys.dbms_session.sleep(1);

  sys.dbms_output.put_line('Time 2: ' || to_char(systimestamp, 'hh24:mi:ss.ff'));

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

  sys.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>

As pointed out by Paul in the comments, if you specify a value of greater than 3600 when using DBMS_SESSION.SLEEP you get a "ORA-38148: invalid time limit specified". This is not the case for the DBMS_LOCK.SLEEP procedure.

SQL> exec dbms_session.sleep(3601);

Error starting at line : 1 in command -
BEGIN dbms_session.sleep(3601); END;
Error report -
ORA-38148: invalid time limit specified
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_SESSION", line 432
ORA-06512: at line 1

SQL> exec dbms_lock.sleep(3601);

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.

For more information see:

Hope this helps. Regards Tim...

Back to the Top.