Forum Stats

  • 3,827,529 Users
  • 2,260,790 Discussions
  • 7,897,291 Comments

Discussions

Accessible SLEEP function

William Robertson
William Robertson Member Posts: 9,567 Bronze Crown
edited Mar 9, 2018 7:53PM in Database Ideas - Ideas

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

ctriebErnanny Figueiredotop.gunManish ChaturvedihimmyLothar FlatzJitendraBPeaslandDBAKiran Pawarfac586Tanzeel MirzaBashar.J.SchnackenbergPkgaverillJagadekarasysassysdbavinaykumar2Geert GruwezLudovicoCaldaraMartin Preissuser8218770ApexBineOzgur Umut Vurgunuser7904656AndrewSayerGregVKevan GellingN.B.Jeffrey KempFatMartinRHemant K ChitalegdanbyGerald Venzl-OracleAparna Dutta-OracleMKJ10930279Jon Theriaultuser29167243203870AnjulSahusensoftDavidMcWhinnieMortenBratenulohmannRobert Marzpattonjg
45
47 votes

Delivered · Last Updated

«1

Comments

  • odie_63
    odie_63 Member Posts: 8,493 Silver Trophy
    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
    William Robertson Member Posts: 9,567 Bronze Crown
    edited Sep 16, 2015 12:19PM
    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 .
    Richard Harrison . Member Posts: 2,065 Gold Trophy

    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
    William Robertson Member Posts: 9,567 Bronze Crown

    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 .
    Richard Harrison . Member Posts: 2,065 Gold Trophy

    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
    William Robertson Member Posts: 9,567 Bronze Crown

    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
    Gerald Venzl-Oracle Member, Moderator Posts: 85 Employee

    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
    William Robertson Member Posts: 9,567 Bronze Crown

    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
    user525156-Oracle Member Posts: 1 Employee

    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.

    William RobertsonJustin Warwick