PSOUG Home Code Snippets Oracle Lookup Oracle Reference Oracle Error Codes Oracle Functions PSOUG Forum


CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle SLEEP
Version 11.1
 
General Information
How Oracle has four separate ways to induce a sleep into PL/SQL. They are:

Note that USER_LOCK package is not part of the default Oracle installation.
Follow this link to install it before running the demo: USER_LOCK Info and Installation.

 

Arguments		 set linesize 121
col package_name format a20
col argument_name format a17
col data_type format a14
col pls_type format a14

SELECT package_name, argument_name, data_type, data_length,
data_precision, pls_type
FROM all_arguments
WHERE object_name = 'SLEEP'
ORDER BY 1;

Precision		 set serveroutput on

DECLARE
 stime TIMESTAMP(9);
 etime TIMESTAMP(9);
BEGIN
  stime := SYSTIMESTAMP;
  dbms_backup_restore.sleep(0.01);
  etime := SYSTIMESTAMP;
  dbms_output.put_line(etime-stime);

  stime := SYSTIMESTAMP;
  dbms_drs.sleep(0.01);
  etime := SYSTIMESTAMP;
  dbms_output.put_line(etime-stime);

  stime := SYSTIMESTAMP;
  dbms_lock.sleep(0.01);
  etime := SYSTIMESTAMP;
  dbms_output.put_line(etime-stime);

  stime := SYSTIMESTAMP;
  user_lock.sleep(1);
  etime := SYSTIMESTAMP;
  dbms_output.put_line(etime-stime);
END;
/

DECLARE
 stime TIMESTAMP(9);
 etime TIMESTAMP(9);
BEGIN
  stime := SYSTIMESTAMP;
  dbms_backup_restore.sleep(1);
  etime := SYSTIMESTAMP;
  dbms_output.put_line(etime-stime);

  stime := SYSTIMESTAMP;
  dbms_drs.sleep(1);
  etime := SYSTIMESTAMP;
  dbms_output.put_line(etime-stime);

  stime := SYSTIMESTAMP;
  dbms_lock.sleep(1);
  etime := SYSTIMESTAMP;
  dbms_output.put_line(etime-stime);

  stime := SYSTIMESTAMP;
  user_lock.sleep(100);
  etime := SYSTIMESTAMP;
  dbms_output.put_line(etime-stime);
END;
/
 
Related Topics
DBMS_BACKUP_RESTORE
DBMS_DRS
DBMS_LOCK
USER_LOCK
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [343 users online]    © 2010 psoug.org