DBMS_LOCK.sleep procedure to suspend the session in Oracle
DBMS_LOCK.sleep procedure suspends the session for a given period of time or specified time in the 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 is the step by step demonstration on DBMS_LOCK.sleep. We will complete it three phase:
In the first phase, we will create a user and try to execute DBMS_LOCK.SLEEP using newly created user.
create user dbsguru identified by dbsguru;
grant create session to dbsguru;
It got failed due to insufficient privileges.
In the 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 required for a specific session/schema to use this package.
grant execute on sys.dbms_lock to dbsguru;
Third phase, in this final phase, we will execute EXEC DBMS_LOCK.sleep package along with other SQL statements where we will see its actually taking to complete (holding session for seconds as the 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
set timing on
set echo on
select username from user_users;
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 the 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 the limit maximum value in seconds 3600 and does 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.
- How to Perform a Health Check of an Oracle Database
- Step by Step How to increase SGA size in Oracle
- How to run sql tuning advisor in Oracle
- How to Check Retention Period of AWR Snapshot in Oracle
- How to Find Last Analyzed date on Tables, Partition, and Subpartition in Oracle
We are sorry that this post was not useful for you!
Let us improve this post!
Tell us how we can improve this post?
Hello and welcome to DBsGuru,
DBsGuru is a group of experienced DBA professionals and serves databases and their related community by providing technical blogs, projects, training.
Technical blogs are the source of vast information not about databases but its related product like middleware, PL/SQL, replication methodology, and so on.
Thanks for the visits!
Share Learn Grow!