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.

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏十月梦想

php创建数据库和数据表

注意:在php5.5以上取消了mysql_query请使用mysqli_query(mysql $link,string $query[,int $resul...

1002
来自专栏奇梦博客

CentOS下mysql数据库常用命令总结 MySQL 参数配置

1331
来自专栏Janti

mysql数据库的常用知识

1094
来自专栏乐沙弥的世界

MySQL 慢查询日志(Slow Query Log)

    同大多数关系型数据库一样,日志文件是MySQL数据库的重要组成部分。MySQL有几种不同的日志文件,通常包括错误日志文件,二进制日志,通用日志,慢查询日...

1012
来自专栏腾讯云Elasticsearch Service

mysql数据实时同步到Elasticsearch

业务需要把mysql的数据实时同步到ES,实现低延迟的检索到ES中的数据或者进行其它数据分析处理。本文给出以同步mysql binlog的方式实时同步数据到ES...

3.6K7
来自专栏枕边书

PHP中的数据库一、MySQL优化策略综述

前些天看到一篇文章说到PHP的瓶颈很多情况下不在PHP自身,而在于数据库。我们都知道,PHP开发中,数据的增删改查是核心。为了提升PHP的运行效率,程序员不光需...

2908
来自专栏Aloys的开发之路

Oracle系统表整理+常用SQL语句收集

-- DBA/ALL/USER/V_$/GV_$/SESSION/INDEX开头的绝大部分都是视图 -- DBA_TABLES意为DBA拥有的或可以访问的所有...

23410
来自专栏杨建荣的学习笔记

闪回归档的简单测试(r8笔记第68天)

11g里面的新特性闪回归档,为历史数据的定制查询提供了一种可能。抽时间学习了一下这个特性,发现还是比较清晰易用。如果使用得当还是很不错的一个特性。 我们来简单模...

3225
来自专栏Ryan Miao

Ubuntu18.04(linux)安装MySQL

Ubuntu18.04 安装mysql或者mariadb之后,发现普通用户和远程都没有权限连接。

1192
来自专栏Ken的杂谈

CentOS 7下InfluxDB部署与使用入门

InfluxDB里存储的数据被称为时间序列数据,InfluxDB存储方式跟传统关系型数据库不同的是:传统关系型数据库通过数据库+表+字段组织数据,InfluxD...

3554

扫码关注云+社区