Accessible SLEEP function

William Robertson

Due no doubt to some accident of history, PL/SQL's sleep() function resides within DBMS_LOCK, a package with nothing to do with timing that happened to need it to implement lock timeouts.

We have probably all put core functions in whatever package we happened to be working on because it seemed less effort at the time, but the effect of this particular design decision is that every account needing to use this basic function within stored PL/SQL must be granted direct execute privilege on a package for managing user-defined locks (or we have to implement a wrapper package in a privileged schema, often hard to justify in a production system). If you are connecting with a read-only production account - for monitoring performance for example - you have to implement it some other way (SQL*Plus PAUSE command, host out to an OS utility etc).

Please can we have a SLEEP function in DBMS_UTILITY or a similar lower-privileged package.

(And in case it's not fixed in 12.1, this time around can we have it coded by someone who knows how many seconds there are in 1000 seconds.)

Oracle have accepted the idea and raised ER: 23557076

Update 2017-05-22: DBMS_SESSION will have a publicly available SLEEP function.

Update 2018-03-09: DBMS_SESSION.SLEEP() is now part of Oracle 18c

odie_63

you have to implement it some other way (SQL*Plus PAUSE command, host out to an OS utility etc).

Or using a simple Java call (if available) :

procedure my_sleep (p_ms in integer) is
language java name 'java.lang.Thread.sleep(int)' ;

William Robertson

you have to implement it some other way (SQL*Plus PAUSE command, host out to an OS utility etc).

Or using a simple Java call (if available) :

procedure my_sleep (p_ms in integer) is
language java name 'java.lang.Thread.sleep(int)' ;

Interesting idea. I couldn't get it to work in my environment (11.2.0.3) though:

declare 
   procedure my_sleep (p_ms in number) is
      language java name 'java.lang.Thread.sleep(int)';
begin
   dbms_output.put_line(to_char(sysdate,'HH24:MI:SS'));
   my_sleep(3);
   dbms_output.put_line(to_char(sysdate,'HH24:MI:SS'));
end;
/

ORA-06550: line 2, column 4:
PLS-00999: implementation restriction (may be temporary) Java methods not permitted here
ORA-06550: line 2, column 4:
PL/SQL: Item ignored

I also tried creating my_sleep() as a standalone procedure but I got "ORA-29538: Java not installed", so that could be the problem.

Richard Harrison .

I only recently discovered this myself - are you aware of USER_LOCK - not loaded by default.

$ORACLE_HOME/rdbms/admin/userlock.sql

Cheers,

Rich

William Robertson

I only recently discovered this myself - are you aware of USER_LOCK - not loaded by default.

$ORACLE_HOME/rdbms/admin/userlock.sql

Cheers,

Rich

Thanks, that does ring a bell. However, I don't see that it really helps. It's still a locking-related package, and we still don't have access to it.

Richard Harrison .

Thanks, that does ring a bell. However, I don't see that it really helps. It's still a locking-related package, and we still don't have access to it.

Hi,

If this package is run in it's grated to public so should just work even in stored plsql as public is 'special'.

Of course you have to get the script run in every production db which is another matter - be nice if it was just there by default.

Cheers,

Rich

William Robertson

Hi,

If this package is run in it's grated to public so should just work even in stored plsql as public is 'special'.

Of course you have to get the script run in every production db which is another matter - be nice if it was just there by default.

Cheers,

Rich

I'd say getting something non-standard installed on every single database is an even greater challenge than requesting grants.

Gerald Venzl-Oracle

Just to add to the comment "(And in case it's not fixed in 12.1, this time around can we have it coded by someone who knows how many seconds there are in 1000 seconds.)".

I did a quick test on 12.1. and if my math is right 16 minutes and 40 seconds sounds about right for 1000 seconds:

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jun 8 11:14:14 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> set timing on;

SQL> exec dbms_lock.sleep(1000);

PL/SQL procedure successfully completed.

Elapsed: 00:16:40.02

So I assume that the issue does not occur in 12.1 but it's hard to say without having more details on what the problem was.

William Robertson

Just to add to the comment "(And in case it's not fixed in 12.1, this time around can we have it coded by someone who knows how many seconds there are in 1000 seconds.)".

I did a quick test on 12.1. and if my math is right 16 minutes and 40 seconds sounds about right for 1000 seconds:

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jun 8 11:14:14 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> set timing on;

SQL> exec dbms_lock.sleep(1000);

PL/SQL procedure successfully completed.

Elapsed: 00:16:40.02

So I assume that the issue does not occur in 12.1 but it's hard to say without having more details on what the problem was.

Gerald, apologies - I thought I had confirmed the bug still existed but it seems it was actually fixed back in 11.2 or earlier.

I was referring to the old and (I thought) well-known timing drift by which sleep times were out by a factor of 1024/1000.

https://groups.google.com/forum/#!topic/comp.databases.oracle.misc/0LVt3VxKB8g

http://www.jlcomp.demon.co.uk/faq/sleep.html

user525156-Oracle

A publicly callable sleep function is now available in a future Oracle Database release. It can be found in DBMS_SESSION.

Documentation for DBMS_SESSION will describe the functionality as per usual.

blessed DBA

Useful

Great