DBMS_LOCK.sleep procedure to suspend the session

2 Comments

4.6
(21)

DBMS_LOCK.sleep procedure suspends the session for a given period of time or specified time in procedure, it will wait to complete procedure then next SQL statement will execute.

DBMS_LOCK.SLEEP (seconds IN NUMBER);

seconds : Amount of time, in seconds, to suspend the session.

The smallest increment can be entered in hundredths of a second; for example, 1.95 is a legal time value.

Below are step by step demonstration on DBMS_LOCK.sleep. We will complete it three phase:


First phase, we will create user and try to execute DBMS_LOCK.SLEEP using newly created user.


create user dbsguru identified by dbsguru;

grant create session to dbsguru;

conn dbsguru/dbsguru
show user

EXEC DBMS_LOCK.sleep(60);


It got failed due to insufficient privileges.


Second phase, we will grant access to user DBSGURU and try to execute DBMS_LOCK.SLEEP again.


By default DBMS_LOCK is not accessible by normal users so grant is require for a specific session / schema to use this package.

grant execute on sys.dbms_lock to dbsguru;
conn dbsguru/dbsguru
show user
EXEC DBMS_LOCK.sleep(60);



Third phase, in this final phase, we will execute EXEC DBMS_LOCK.sleep package along with other SQL statements where we will see its actual taking to complete (holding session for seconds as specified value in package) 1 minute and then moving to next SQL statement and so on.


We will create one small .sql file as below to validate

vi DBMS_LOCK_sleep.sql


set timing on
set echo on
show user
EXEC DBMS_LOCK.sleep(60);
select username from user_users;
EXEC DBMS_LOCK.sleep(60);
select count(1) from v$session;


As we can see above procedure is taking to complete one minute of time as specified value i.e the procedure suspends the session for the specified number of seconds. to execute next SQL statement.

Note: 1. EXEC DBMS_LOCK.sleep is deprecated from 18c but still available for backward compatibility.


2. We can use procedure DBMS_SESSION.SLEEP in 18c where it’s limit maximum value in seconds 3600 and not require additional privileges for normal users.



This document is only for learning purpose and always validate in the LAB environment first before applying in the LIVE environment.


Hope so you like this article
!
Please share your valuable feedback/comments/subscribe and follow us below and don’t forget to click on the bell icon to get the latest update. Click here to know more about our pursuit.

 

 1,785 total views,  2 views today

How useful was this post?

Click on a star to rate it!

Average rating 4.6 / 5. Vote count: 21

No votes so far! Be the first to rate this post.

As you found this post useful...

Follow us on social media!

We are sorry that this post was not useful for you!

Let us improve this post!

Tell us how we can improve this post?

Spread the love

2 thoughts on “<a rel="bookmark" href="https://dbsguru.com/dbms_lock-sleep-procedure-to-suspend-the-session/">DBMS_LOCK.sleep procedure to suspend the session</a>”

  1. Nice option..when we fetch report from the database from app server.. but I doubt it will create holding session n in db it will appear as blocking session which will misguide the dba. So on general basis dba will kill tht holding session from dba sessions.. it is useful when load is low on database. Which lock it will use that have to check.

    1. Thanks & appreciate Vishal for your concern!! No it will not hold any session in term of blocking sessions since here using package DBMS_LOCK.sleep and same has been tested. So its safe to use according to require and appreciate if you also validate same in lab / test environment and let us know if any concerns on same.

      Thanks.
      Team DbsGuru

Leave a Reply

Your email address will not be published. Required fields are marked *

nineteen + 5 =

Follow us on Social Media
Open chat
1
Contact Us:
Hi,

Greetings of the day! How can we help you?

Thanks.
Team DBsGuru