Oracle – Quick Tip – Sleep In Your Code (Equivalent of SQL Server “WAITFOR DELAY”)

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.


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.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s