在过程，包，函数，触发器中调用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.
更多参考
有关Oracle RAC请参考
使用crs_setperm修改RAC资源的所有者及权限 使用crs_profile管理RAC资源配置文件 RAC 数据库的启动与关闭 再说 Oracle RAC services Services in Oracle Database 10g Migrate datbase from single instance to Oracle RAC Oracle RAC 连接到指定实例 Oracle RAC 负载均衡测试(结合服务器端与客户端) Oracle RAC 服务器端连接负载均衡(Load Balance) Oracle RAC 客户端连接负载均衡(Load Balance) ORACLE RAC 下非缺省端口监听配置(listener.ora tnsnames.ora)
ORACLE RAC 监听配置 (listener.ora tnsnames.ora) 配置 RAC 负载均衡与故障转移 CRS-1006 , CRS-0215 故障一例
基于Linux (RHEL 5.5) 安装Oracle 10g RAC
使用 runcluvfy 校验Oracle RAC安装环境
有关Oracle 网络配置相关基础以及概念性的问题请参考：
配置非默认端口的动态服务注册
配置sqlnet.ora限制IP访问Oracle Oracle 监听器日志配置与管理
设置 Oracle 监听器密码(LISTENER) 配置ORACLE 客户端连接到数据库
有关基于用户管理的备份和备份恢复的概念请参考
Oracle 冷备份 Oracle 热备份 Oracle 备份恢复概念 Oracle 实例恢复 Oracle 基于用户管理恢复的处理 SYSTEM 表空间管理及备份恢复 SYSAUX表空间管理及恢复 Oracle 基于备份控制文件的恢复(unsing backup controlfile)
有关RMAN的备份恢复与管理请参考
RMAN 概述及其体系结构 RMAN 配置、监控与管理 RMAN 备份详解 RMAN 还原与恢复 RMAN catalog 的创建和使用 基于catalog 创建RMAN存储脚本 基于catalog 的RMAN 备份与恢复 RMAN 备份路径困惑 使用RMAN实现异机备份恢复(WIN平台) 使用RMAN迁移文件系统数据库到ASM linux 下RMAN备份shell脚本 使用RMAN迁移数据库到异机
有关ORACLE体系结构请参考
Oracle 表空间与数据文件 Oracle 密码文件 Oracle 参数文件 Oracle 联机重做日志文件(ONLINE LOG FILE) Oracle 控制文件(CONTROLFILE) Oracle 归档日志 Oracle 回滚(ROLLBACK)和撤销(UNDO) Oracle 数据库实例启动关闭过程 Oracle 10g SGA 的自动化管理 Oracle 实例和Oracle数据库(Oracle体系结构)