Although the need for this is rare, it is sometimes useful to make an Oracle session “sleep” for a certain amount of time before proceeding
Where seconds can be a decimal value. Eg: 0.5 is ½ second and 5.25 is 5 ¼ seconds.
You do need to grant EXECUTE on the DBMS_LOCK package to the appropriate USER/ROLE.
GRANT EXECUTE ON DBMS_LOCK TO [USER/ROLE]
Here are a few circumstances you may use it in
- You are testing something and need introduce artificial delays
- You have hit a condition in your code that might clear after a while and you want to wait it out
- You want to run something periodically while waiting in between (there are better ways to do this)
Here is a case where I used it:
When working with partitioned tables, where 10 or more sessions were working with the same table in parallel trying to truncate, exchange partitions simultaneously, the underlying table was experiencing locking issues that were bound clear in a few seconds but I did not want the code to fail in the middle of a long running process. Sleep came in handy to wait for a bit before retrying (for a set number of retries before giving up).
Warning: Use of empty loops as a wait mechanism will waste CPU cycles and is an extremely unreliable way to wait a certain amount of time. Whenever possible, use the native mechanism provided by the operating system or product.