在過程，包，函數，觸發器中調用Oracle相關動態性能視圖時，需要授予適當的權限，否則會收到表和視圖不存在的錯誤提示。即使你可以單獨查詢這些視圖。因為動態性能視圖依賴於底層表，無法直接對其授予權限。下面就是這個現象相關的例子。
1、過程調用動態視圖無法成功編譯的示例
SQL> select * from v$version where rownum<2;
BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.5.0 - 64bit Production
SQL> show user;
USER is "GX_ADM"
SQL> CREATE OR REPLACE PROCEDURE tst
2 AS
3 v_usr VARCHAR2(30);
4 BEGIN
5 SELECT username INTO v_usr FROM v$process WHERE ROWNUM < 2;
6 DBMS_OUTPUT.put_line ('Username is ' || v_usr);
7 END;
8 /
Warning: Procedure created with compilation errors.
SQL> show errors;
Errors for PROCEDURE TST:
LINE/COL ERROR
-------- -----------------------------------------------------------------
5/1 PL/SQL: SQL Statement ignored
5/33 PL/SQL: ORA-00942: table or view does not exist
2、分析與解決
--根據上面提示的錯誤信息，咋一看就是表和視圖不存在
--而實際上動態性能視圖是一個同義詞，並非真正的視圖，下面嘗試使用sys帳戶對其賦予權限到所需用戶
--收到了ORA-02030錯誤信息，也就是說只能對固定的表和視圖進行權限賦予
SQL> conn / as sysdba
Connected.
SQL> grant select on v$process to gx_adm;
grant select on v$process to gx_adm
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views
SQL> conn gx_adm/xxx -->使用gx_adm用戶連接數據庫
Error accessing PRODUCT_USER_PROFILE
Warning: Product user profile information not loaded!
You may need to run PUPBLD.SQL as SYSTEM
Connected.
--用戶本身是可以訪問該動態性能視圖的
SQL> select username FROM v$process WHERE ROWNUM < 2 and username is not null;
USERNAME
---------------
oracle
SQL> conn / as sysdba
Connected.
--Author : Leshami
--Blog : http://blog.csdn.net/leshami
--基於真實的視圖授予權限
SQL> grant select on v_$process to gx_adm;
Grant succeeded.
--下面再次編譯正常
gx_adm@CNMMBO> alter procedure tst compile;
Procedure altered.
--我們也可以通過執行計劃來查看底層訪問對象為X$KSUPR，這也就是為什麼前面授權失敗的原因
SQL> set autot trace exp;
SQL> select username FROM v$process WHERE ROWNUM < 2 and username is not null;
Execution Plan
----------------------------------------------------------
------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 35 | 0 (0)|
| 1 | COUNT STOPKEY | | | | |
| 2 | FIXED TABLE FULL| X$KSUPR | 1 | 35 | 0 (0)|
------------------------------------------------------------------
3、Metalink文章(Doc ID 1062335.6)
ORA-00942: table or view does not exist
Solution Description:
Grant the owner of the stored procedure select directly on the needed V$ view.
SQL> GRANT SELECT on V_$SESSION to <user_name>;
更多參考
有關Oracle RAC請參考
有關Oracle 網絡配置相關基礎以及概念性的問題請參考：
有關基於用戶管理的備份和備份恢復的概念請參考
有關RMAN的備份恢復與管理請參考
有關ORACLE體系結構請參考
