编译正在调用的函数等待library cache pin

create or replace function get_sal(name1 number) return number is
  v_sal number;
begin
  select name1+20 into v_sal from dual;
  /*dbms_lock.sleep(30);*/
  return v_sal;
end get_sal;

select get_sal(11) from dual;


SQL> create or replace function get_sal(name1 number) return number is
  2    v_sal number;
  3  begin
  4    select name1+20 into v_sal from dual;
  5    dbms_lock.sleep(30);
  6    return v_sal;
  7  end get_sal;
  8  /
Warning: Function created with compilation errors

SQL> show err
Errors for FUNCTION TLCB.GET_SAL:
LINE/COL ERROR
-------- -------------------------------------
5/3      PLS-00201: 必须声明标识符 'DBMS_LOCK'
5/3      PL/SQL: Statement ignored

需要赋权:


SQL> grant execute on dbms_lock to tlcb;

授权成功。


SESSION 69
select * from v$mystat a where rownum<2;
--69



SESSION 71
create or replace function get_sal(name1 number) return number is
  v_sal number;
begin
  select name1 + 20 into v_sal from dual;
   DBMS_LOCK.SLEEP(10);
  return v_sal;
end get_sal;



SELECT SID,EVENT FROM V$SESSION WHERE SID IN (69,71)

1	69	PL/SQL Developer	plsqldev.exe	PL/SQL lock timer
2	71	PL/SQL Developer	plsqldev.exe	library cache pin