PL/SQL，創建procedure或function時，提示V$視圖不存在。

用戶可以在sql中查詢V$視圖，但是創建procedure或function時，提示V$視圖不存在。

SQL> create function get_stat_val
  2  (
  3    p_name in varchar2
  4  ) return number
  5  as
  6    v_value number;
  7  begin
  8    select m.value into v_value from v$mystat m,v$statname s
  9    where m.statistic#=s.STATISTIC#
 10    and s.name=p_name;
 11    return v_value;
 12  end;
 13  /
 
Warning: Function created with compilation errors
 
SQL> sho err
Errors for FUNCTION WEBCUS.GET_STAT_VAL:
 
LINE/COL ERROR
-------- -----------------------------------------------
8/47     PL/SQL: ORA-00942: table or view does not exist
8/3      PL/SQL: SQL Statement ignored

查詢用戶的具有的角色，具有DBA角色

SQL> select * from dba_role_privs where grantee='WEBCUS';
 
GRANTEE    GRANTED_RO ADMIN_OPTION DEFAULT_ROLE
---------- ---------- ------------ ------------
WEBCUS     DBA        NO           YES

為用戶授予V$視圖的select權限

SQL> grant select on v$mystat to webcus;
grant select on v$mystat to webcus
                *
ERROR at line 1:
ORA-02030: can only select from fixed tables/views

v$开头的视图是v_$的同义词，对以v$开头的视图，不能直接grant。

http://blog.youkuaiyun.com/wh62592855/article/details/4844522

SQL> grant select on v_$mystat to webcus;

Grant succeeded.

SQL> grant select on v_$statname to webcus;

Grant succeeded.

重啟創建function

SQL> create function get_stat_val
  2  (
  3    p_name in varchar2
  4  ) return number
  5  as
  6    v_value number;
  7  begin
  8    select m.value into v_value from v$mystat m,v$statname s
  9    where m.statistic#=s.STATISTIC#
 10    and s.name=p_name;
 11    return v_value;
 12  end;
 13  /
 
Function created
 
SQL> select get_stat_val('redo size') from dual;
 
GET_STAT_VAL('REDOSIZE')
------------------------
                  108944