ORA-00942: table or view does not exist

      在过程，包，函数，触发器中调用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-942 when select from any v$view within stored PL/SQL procedure (Doc ID 1062335.6) Problem Description: ~~~~~~~~~~~~~~~~~~~~ You are selecting from a system view, such as V$SESSION, from within a PL/SQL stored procedure and you receive an ORA-00942 error.

    ORA-00942: table or view does not exist         Cause: The table or view entered does not exist, a synonym                that is not allowed here was used, or a view was                referenced where a table is required.  Existing user                tables and views can be listed by querying the data                dictionary.  Certain privileges may be required to                access the table.  If an application returned this                message, the table the application tried to access                does not exist in the database, or the application                does not have access to it.        Action: Check each of the following:                - the spelling of the table or view name.                - that a view is not specified where a table is                  required.                - that an existing table or view name exists.  Contact                  the database administrator if the table needs to be                  created or if user or application privileges are                  required to access the table.                Also, if attempting to access a table or view in another                schema, make certain the correct schema is referenced                and that access to the object is granted.

Problem Explanation: ~~~~~~~~~~~~~~~~~~~~ The ORA-00942 is produced because the privilege to use the V$ views has been granted to the user via a role, roles are not in effect within stored PL/SQL procedures.

Problem References: ~~~~~~~~~~~~~~~~~~~ Oracle7 Server Application Developer's Guide

Search Words: ~~~~~~~~~~~~~ ORA-942

Solution Description: ~~~~~~~~~~~~~~~~~~~~~

Grant the owner of the stored procedure select directly on the needed V$ view. (Remember that the grant must be made on the actual table or view name, not the synonym):

SQL> GRANT SELECT on V_$SESSION to <user_name>;

Solution Explanation: ~~~~~~~~~~~~~~~~~~~~~ Granting the owner of the PL/SQL stored procedure select directly on the required V$ view will allow the select to complete successfully.