sitemap

RSS地图

收藏本站

设为首页

Oracle研究中心

当前位置:Oracle研究中心 > 故障案例 >

【案例】Oracle报错ORA-02030产生原因和MOS官方解决办法

时间:2016-10-30 10:48   来源:Oracle研究中心   作者:HTZ   点击:

天萃荷净 运维DBA反映遇到Oracle报错ORA-02030 WHEN GRANTING SELECT ON V$ VIEW,结合MOS官方文章分享该报错产生的原因和解决办法。
Modified:02-Mar-2013Type:PROBLEM ?
"Checked for relevance on 24 October 2007"

Problem Description: 
==================== 

When trying to grant select on a v$ view as sys, to a user that is not able to
select on those views, you receive the following error:

   ORA-02030: can only select from fixed tables/views when granting
   select on a v$view.  

   Error:  ORA 2030
   Text:   can only select from fixed tables/views
   Cause:  An operation other than SELECT on a fixed dynamic performance
           table or view was attempted.
   Action: Remove the fixed table or view name from the SELECT statement.

You can select on the view but cannot grant any object privileges on the view.


Solution Description: 
====================== 

Granting select on the underlying V_$ view instead of the V$ synonym will
allow you to grant select on the object. 

Example:

    grant select on v_$database to scott;  

 
Explanation: 
============
 
The actual view created on the dynamic performance table is named V_$(view
name).  The V$(view name) is only a synonym and not an actual fixed table or a
view.  Thus, when you try to grant select on the object you will receive the
error:  ORA-02030: can only select from fixed tables/views.
Grant Select on V_$DATABASE

For Example:
-------------

SVRMGR> grant select on v$database to scott;
grant select on v$database to scott
                *
ORA-02030: can only select from fixed tables/views

SVRMGR> select OWNER, OBJECT_TYPE,OBJECT_NAME from dba_objects
        where object_name='V$DATABASE';


OWNER    OBJECT_TYPE   OBJECT_NAME
------   -----------   -----------
PUBLIC   SYNONYM       V$DATABASE

Note:  This tells us that V$DATABASE is just a synonym.

SVRMGR> select SYNONYM_NAME,TABLE_OWNER,TABLE_NAME from dba_synonyms
        where synonym_name='V$DATABASE';

SYNONYM_NAME   TABLE_OWNER    TABLE_NAME
------------   -----------    ----------
V$DATABASE     SYS         V_$DATABASE

Note:  This tells us the V$DATABASE is synonym for SYS.V_$DATABASE,
       so we need to grant select on the v_$database.

SVRMGR> grant select on v_$database to scott;
Statement processed.


Search Words:
=============

ORA-2030
本文固定链接: http://www.htz.pw/2014/08/03/ora-02030-when-granting-select-on-v-view.htOracle?о?????ml | 认真就输

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【案例】Oracle报错ORA-02030产生原因和MOS官方解决办法

本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/1112.html

Oracle研究中心

关键词:

ORA-02030

ORA-02030 WHEN GRANTING SELECT ON V VIEW