Recently a colleague sent me a quick instant message asking if I knew of any way in Oracle to make a query run long enough to test a “cancel query” function he was writing in Java. He had tried several queries that usually produced the results he was looking for, however there wasn’t enough data in his test environment to reproduce the wait time he needed. I thought about it for a minute and then proposed the following solution.
Oracle provides the DBMS_LOCK package which allows users to manage their own locks. One handy subprogram in this package in the SLEEP method, which suspends the current session for the specified number of seconds. I have used DBMS_LOCK.SLEEP a number of times in the past so I knew this would do the trick.