Oracle “sleep” procedure: DBMS_LOCK.SLEEP

September 7, 2010 by Michael

There’s a nice little “sleep” procedure in Oracle: A procedure that stops the execution of the current thread for n seconds.

Strangely, this method can be called in SQL*Plus like so:

EXEC dbms_lock.sleep(10);

but not in another stored procedure or function like so

CREATE OR REPLACE PROCEDURE FOOBAR AS
BEGIN
  DBMS_LOCK.SLEEP(1);
END;
/

To use “sleep” in your procedures or functions, login as administrator to your database (or ask you admin to to so) and create the following objects:

CREATE OR REPLACE PROCEDURE sleep(seconds NUMBER) AS
BEGIN
  DBMS_LOCK.SLEEP(seconds);
END;
/
 
CREATE OR REPLACE public synonym sleep FOR sleep;
 
GRANT EXECUTE ON sleep TO public;

and in your procedure just use “sleep”.

18 comments

  1. Marc wrote:

    Man you saved my day! Had exactly that darn problem and couldn’t figure out why. Thanks a lot! 🙂

    Posted on October 21, 2010 at 3:28 PM | Permalink
  2. Michael wrote:

    You’re welcome, Marc.

    Nice Blog, btw!

    Posted on October 21, 2010 at 3:49 PM | Permalink
  3. Erik Hymel wrote:

    You can call it from a procedure. I suspect you have execute on dbms_lock through a role, but to call the procedure the permission needs to be granted directly to the user.

    Posted on April 26, 2011 at 5:47 PM | Permalink
  4. Amin Adatia wrote:

    It seems that in order to use DBMS_LOCK you need to be granted EXECUTE on DBMS_LOCK by SYS (or SYSTEM?). Wht the package is just not publicly available out-of-the-box is unknown. No idea what is so terrible in the package.

    Posted on February 19, 2012 at 6:57 PM | Permalink
  5. Michael wrote:

    Exactly that didn’t work for me. I had to create this superfluous alias, for whatever reason. You see i have grant on this as well.

    Posted on February 19, 2012 at 7:16 PM | Permalink
  6. Muneer Nawaz wrote:

    This is very useful, thnx for sharing this information.

    Posted on May 30, 2013 at 11:06 AM | Permalink
  7. Michael wrote:

    You’re welcome Muneer.

    Posted on May 31, 2013 at 10:22 AM | Permalink
  8. karan mehta wrote:

    Hi, I created a procedure like this.
    create or replace procedure proc_sleep
    as
    begin
    dbms_output.put_line(‘start’);
    dbms_lock.sleep(10);
    dbms_output.put_line(‘end’);
    end;
    Generally, it should first print start then go off to sleep for 10 seconds and then it should print end. However when I run this, it first goes off to sleep for 10 seconds and then it prints start and end. Can you please let me know the reason behind this and how to resolve it. I am using SQL developer and oracle 10g express edition.

    Posted on September 17, 2014 at 4:04 PM | Permalink
  9. Michael wrote:

    Hi Karan,

    your procedure is correct.

    PL/SQL isn’t SQL. So if you call this, context switches to PL/SQL. dbms_output writes to a buffer and that buffer is printed when the context switches back, that is, after your proc ends. See also “Operational Notes” in the docs http://docs.oracle.com/cd/B193.....m#i1000634 or this answer on Stackoverflow: http://stackoverflow.com/a/1472673

    Posted on September 17, 2014 at 4:17 PM | Permalink
  10. karan mehta wrote:

    Hi Michael,
    Thanks! this helped me. I wrote a procedure with an if condition and then made it sleep and then i again had a if condition. Avoided the dbms_output and it worked properly. I have another query.

    In a procedure, I have created a sequence at the start of the procedure. Now in the end of my procedure, I want to reset the value of the sequence to 1. Tried using execute immediate but it works individually but not when I invoke it in my procedure. Can you please let me know any way of resetting my sequence?

    Posted on September 18, 2014 at 3:20 PM | Permalink
  11. Michael wrote:

    Hi.

    I’d probably try this http://stackoverflow.com/a/93633/1547989

    Posted on September 18, 2014 at 3:29 PM | Permalink
  12. karan mehta wrote:

    Hi Michale, yes this link did help. Now I am able to reset the sequence. Thanks once again!! Now, a further query. Can you please let me know how to check the diskspace by running a query in a procedure? In one project I have to check the diskspace(in percentage). A link would also do.

    Posted on September 19, 2014 at 8:47 AM | Permalink
  13. Michael wrote:

    Let me google that for you 😉

    select df.tablespace_name "Tablespace",
    totalusedspace "Used MB",
    (df.totalspace - tu.totalusedspace) "Free MB",
    df.totalspace "Total MB",
    round(100 * ( (df.totalspace - tu.totalusedspace)/ df.totalspace))
    "Pct. Free"
    from
    (select tablespace_name,
    round(sum(bytes) / 1048576) TotalSpace
    from dba_data_files 
    group by tablespace_name) df,
    (select round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name
    from dba_segments 
    group by tablespace_name) tu
    where df.tablespace_name = tu.tablespace_name ;
    
    Posted on September 19, 2014 at 10:49 AM | Permalink
  14. karan mehta wrote:

    Hey Michael, thanks for this one; was a little busy with some another projects hence it took me long to have a look at this one. This query also worked and thanks for this as well. I have another query. My string can store max of 4000 characters, however I have approx 5000 characters to store. When I try to store it, it gives me an error saying that it is more than 4000 characters hence the data cannot be stored. Is it possible to store 4000 characters out of that 5000 characters without an error? I m ok if the remaining 1000 characters are not inserted!

    Posted on October 6, 2014 at 6:20 AM | Permalink
  15. Novita puspa dewi wrote:

    I still work with my stored procedure, but it has dbms lock sleep in order to make 2 stored procedures meet at the same time. I want to implements management transaction within the store procedure. The problem is when I executed the store procedure with dbms lock sleep, it won’t work and error : dbms lock sleep must be declared.

    Posted on October 16, 2016 at 9:21 AM | Permalink
  16. Michael wrote:

    Hi, see Novita, please read the post again. Create a delegating procedure as admin and a public synonym for that. Also in Comment #4.

    Posted on October 16, 2016 at 9:23 AM | Permalink
  17. Vishal wrote:

    Hi,
    Could you please help me in writing a procedure which will create two jobs in such a way that when the first job gets completed then the second job is created. the second job will not get created until and unless first job gets completed.

    Posted on October 3, 2017 at 7:58 AM | Permalink
  18. gali wrote:

    to use DBMS_LOCK.sleep in a procedure/function you need just to have correct definition of AUTHID and you can execute it in an anonymous block.

    CREATE OR REPLACE PROCEDURE someproc
    AUTHID CURRENT_USER
    AS
    BEGIN
    …some other stuff…
    EXECUTE IMMEDIATE ‘BEGIN DBMS_LOCK.sleep(3); END;’;
    …some other stuff…
    END;
    /

    Posted on March 2, 2018 at 11:31 PM | Permalink
Post a Comment

Your email is never published. We need your name and email address only for verifying a legitimate comment. For more information, a copy of your saved data or a request to delete any data under this address, please send a short notice to michael@simons.ac from the address you used to comment on this entry.
By entering and submitting a comment, wether with or without name or email address, you'll agree that all data you have entered including your IP address will be checked and stored for a limited time by Automattic Inc., 60 29th Street #343, San Francisco, CA 94110-4929, USA. only for the purpose of avoiding spam. You can deny further storage of your data by sending an email to support@wordpress.com, with subject “Deletion of Data stored by Akismet”.
Required fields are marked *