The Sleep Function in Oracle
The sleep function in Oracle is in reality a procedure you can include in a function rather than a function itself. Confusion often occurs because of striking similarities between the two, with the only real difference being that a procedure follows instructions that do not always return a value, while a function must always ends with a return value. Adding PL/SQL statements that put an Oracle database “to sleep” is a common administrative task and depending on your preference, end users can also use a sleep option.
Other People Are Reading
-
Identification
-
Putting an Oracle database to sleep means exactly what it says. The statements you write cause the database to suspend normal operations or stop working, sometimes for as little as a few hundredths of a second. For example, end users can add a sleep procedure to pause the database before it inserts new information into a table. Database administrators can use a sleep function to tell the database to wait a specific amount of time before attempting to rerun a failed action, or to pause the database while it performs a routine backup.
Process
-
Oracle provides you with three options if you are an administrator and one option if you are an end user for coding and using the sleep procedure in PL/SQL statements. The three administrative commands are dbms_backup_restore, dbms_drs and dbms_lock and the end user command is user_lock. The dbms_backup_restore option stops the database during backup and restore procedures, dbms_drs – or distributed resource scheduling – can suspend a session during resource optimization and both dbms and user_lock are useful for providing exclusive use to a specific machine or terminal for a limited time, enforcing a time limit for a read_lock and for synchronizing applications.
-
Syntax
-
While the actual command for each differs according to the option you choose, the general syntax format for writing a sleep procedure starts by creating the procedure using the statement “CREATE PROCEDURE_sleep [(sleepTime IN INTEGER)] IS.” The beginning part of the statement creates the procedure and the information within the square brackets identifies its parameters, which in this case is the name of the procedure, the IN identifier that tells Oracle the procedure can also be used within a function and INTEGER which defines the data type. Start the procedure with a simple “BEGIN” statement and on the following line identify the option and its parameters, such as SYS.DBMS_BACKUP_RESTORE.SLEEP(sleepTime_in_seconds = 5.01) before ending the procedure with the END statement:
CREATE PROCEDURE_sleep [(sleepTime_in_minutes IN INTEGER)]
IS
BEGIN
SYS.DBMS_BACKUP_RESTORE.SLEEP(sleepTime_in_minutes = 15.01);
END;
/
Considerations
-
When it comes to granting EXECUTE privileges to end users for the user_lock.sleep command, understand that not only will end users have the ability to write a sleep procedure, but also all locking procedures associated with this command. In addition, the size and setup of your Oracle database may prevent you from granting EXECUTE privileges to all end users. Because of this, Oracle recommends granting lock privileges according to need or role within the company.
-
References
- Photo Credit Hemera Technologies/PhotoObjects.net/Getty Images
