Skip to content
accelerando

Oracle “sleep” procedure: DBMS_LOCK.SLEEP

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

13 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 21-Oct-10 at 3:28 pm | Permalink
  2. Michael wrote:

    You’re welcome, Marc.

    Nice Blog, btw!

    Posted on 21-Oct-10 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 26-Apr-11 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 19-Feb-12 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 19-Feb-12 at 7:16 pm | Permalink
  6. Muneer Nawaz wrote:

    This is very useful, thnx for sharing this information.

    Posted on 30-May-13 at 11:06 am | Permalink
  7. Michael wrote:

    You’re welcome Muneer.

    Posted on 31-May-13 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 17-Sep-14 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 17-Sep-14 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 18-Sep-14 at 3:20 pm | Permalink
  11. Michael wrote:

    Hi.

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

    Posted on 18-Sep-14 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 19-Sep-14 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 19-Sep-14 at 10:49 am | Permalink

Post a Comment

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

*