DBMS_LOCK.SLEEP DBMS_SESSION.SLEEP 测试网络超时 timeout

测试网络超时 

create table temp(id1 number,sysdate1 date)

test:

set serveroutput on

declare 
  i integer;
begin
   insert into temp(1,sysdate);
   dbms_output.put_line(to_char(sysdate,'yyyy-MM-dd HH24:MI:SS'));
   sys.DBMS_LOCK.SLEEP(4000);
   commit;
   dbms_output.put_line(to_char(sysdate,'yyyy-MM-dd HH24:MI:SS'));
   insert into temp(1,sysdate);
   commit;
end;
/

介绍

　　在 18c 中不推荐使用 DBMS_LOCK.SLEEP，推荐使用 DBMS_SESSION.SLEEP，并且无需额外授权即可使用。
　　SLEEP会将会话暂停指定的秒数。

实践证明

SQL> select * from user_role_privs;

USERNAME   GRANTED_RO ADMIN_OPTI DELEGATE_O DEFAULT_RO OS_GRANTED COMMON     INHERITED
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
TEST       CONNECT    NO         NO         YES        NO         NO         NO
TEST       RESOURCE   NO         NO         YES        NO         NO         NO

SQL> show user
USER is "TEST"
SQL> SET SERVEROUTPUT ON ;
SQL> BEGIN
  2  DBMS_OUTPUT.PUT_LINE('Start ' || to_char(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
  3  dbms_session.sleep(5);
  4  DBMS_OUTPUT.PUT_LINE('End ' || to_char(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
  5  END;
  6  /
Start 2020-03-05 10:49:36
End 2020-03-05 10:49:41

PL/SQL procedure successfully completed.

　　默认情况下无法使用 DBMS_LOCK，必须为用户授予 dbms_lock 包的执行权限。DBMS_SESSION.SLEEP 不需要任何额外授权。

　　如果在使用DBMS_SESSION.SLEEP时指定的值大于3600，则会得到“ORA-38148：指定的时间限制无效”。
　　DBMS_LOCK.SLEEP过程不会报此错误。

SQL> EXEC DBMS_SESSION.sleep(3601);
BEGIN DBMS_SESSION.sleep(3601); END;

*
ERROR at line 1:
ORA-38148: invalid time limit specified
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_SESSION", line 432
ORA-06512: at line 1

SQL> !oerr ora 38148
38148, 00000, "invalid time limit specified"
// *Cause: Specified time limit value was not a positive integer.
// *Action: Specify a positive integer value.

SQL> EXEC DBMS_LOCK.sleep(3601);
PL/SQL procedure successfully completed.

Applies to:

Oracle Database - Enterprise Edition - Version 18.1.0.0.0 to 19.3.0.0.0 [Release 18 to 19]
Information in this document applies to any platform.

Goal

QUESTION
========

oracle DB 18.3.0.0.0

Is DBMS_LOCK.SLEEP parameter is available  in Oracle 18C ?
 

Solution

ANSWER
========
DBMS_LOCK.SLEEP is depreciated in Oracle 18c and DBMS_SESSION.SLEEP is introduced, and is available with no additional grants needed.

SLEEP procedure suspends the session for the specified number of seconds.



Example:

CDB$ROOT@CDB18> SET SERVEROUTPUT ON ;
BEGIN
DBMS_OUTPUT.PUT_LINE('Start ' || to_char(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
dbms_session.sleep(5);
DBMS_OUTPUT.PUT_LINE('End ' || to_char(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
END;
/


DBMS_LOCK procedure was  not accessible by default and user must be granted execute permission on the dbms_lock package. DBMS_SESSION.SLEEP does not need any additional grants.

Refer:

https://docs.oracle.com/en/database/oracle/oracle-database/18/arpls/DBMS_LOCK.html#GUID-ABF571D1-443D-4528-BDD2-50768B4D1898
https://oracle-base.com/articles/18c/dbms_session-sleep-18c