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

14 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
Post a Comment

Your email is never published nor shared. Required fields are marked *