ORA-02030問題解決方法

給普通使用者賦予對v$檢視的查詢許可權，結果報 ORA-02030 錯誤
ORA-02030: 只能從固定的表/檢視查詢(ORA-02030: can only select from fixed tables/views)

問題背景:
在一個dba使用者下的儲存過程中需要使用v$的檢視(v$client_stats)

使用命令列測試:
SQL> variable total number;
SQL> begin
  2    select count(1) into :total from v$client_stats;
  3    dbms_output.put_line('total = '|| :total);
  4  end;
  5  /
 
PL/SQL procedure successfully completed
total
---------
0

這樣並沒有問題，於是編輯成過程執行

使用過程
SQL> create or replace procedure p_test
  2  authid current_user
  3  as
  4    v_all   number := 9999;
  5  begin
  6    select count(1)
  7      into v_all from v$client_stats;
  8  
  9    dbms_output.put_line('v_all =' ||v_all );
 10  end;
 11  /
 
Warning: Procedure created with compilation errors

具體錯位資訊
Compilation errors for PROCEDURE ZHANGBIN.P_TEST

Error: PL/SQL: ORA-00942: 表或檢視不存在
Line: 9
Text: FROM v$client_stats;


Error: PL/SQL: SQL Statement ignored
Line: 7
Text: SELECT COUNT(1)

此時需要給使用者賦查詢許可權(於是產生了ORA-02030錯誤)

SQL> show user;
User is "SYS"

SQL> grant select on v$client_stats to zhangbin;
 
grant select on v$client_stats to zhangbin
 
ORA-02030: 只能從固定的表/檢視查詢

解決方法:

SQL> select * from dba_synonyms t where t.synonym_name = 'V$CLIENT_STATS';
 
OWNER                          SYNONYM_NAME                   TABLE_OWNER                    TABLE_NAME                     DB_LINK
------------------------------ ------------------------------ ------------------------------ ------------------------------ -----------
PUBLIC                         V$CLIENT_STATS                 SYS                            V_$CLIENT_STATS
 
SQL> grant select on V_$CLIENT_STATS to zhangbin;
 
Grant succeeded

切換到普通使用者
SQL> show user;
User is "zhangbin"

SQL> create or replace procedure p_test
  2  as
  3    v_all      number :=9999;
  4  begin
  5    select count(1)
  6      into v_all
  7      from v$client_stats;
  8    dbms_output.put_line('v_all =' ||v_all );
  9  end;
 10  /
 
Procedure created
 
SQL> set serveroutput on
SQL> exec p_test;
 
v_all =0
 
PL/SQL procedure successfully completed