ORA-02030: can only select from fixed tables/views

栏目: 数据库 · 发布时间: 6年前

内容简介:关于ORA-02030错误介绍如下,也是就是对于fixed tables 或fixed views只能进行SELECT查询,不能做SELECT之外的任何操作[oracle@DB-Server ~]$ oerr ora 2030

有时候给一些普通用户授予查询系统对象(例如dynamic performance views)权限时会遇到 ORA-02030: can only select from fixed tables/views ,如下所示:

SQL> grant select on v$session to test;
grant select on v$session to test
                *
ERROR at line 1:
ORA-02030: can only select from fixed tables/views

关于ORA-02030错误介绍如下,也是就是对于fixed tables 或fixed views只能进行SELECT查询,不能做SELECT之外的任何操作

[oracle@DB-Server ~]$ oerr ora 2030

02030, 00000, "can only select from fixed tables/views"

// *Cause:   An attempt is being made to perform an operation other than

//          a retrieval from a fixed table/view.

// *Action:   You may only select rows from fixed tables/views.

关于V$ Views的介绍如下:

V$ Views

The actual dynamic performance views are identified by the prefix V_$. Public synonyms for these views have the prefix V$. Database administrators and other users should access only the V$ objects, not the V_$ objects.

The dynamic performance views are used by Oracle Enterprise Manager, which is the primary interface for accessing information about system performance. After an instance is started, the V$ views that read from memory are accessible. Views that read data from disk require that the database be mounted, and some require that the database be open.

我们查询发现V$SESSION,V$DBLINK都是fixed views,而且v$这类我们经常查的视图都是v_$开头视图的同义词。

SQL> SELECT * FROM V$FIXED_TABLE WHERE NAME IN( 'V$SESSION','V$DBLINK');
 
NAME                            OBJECT_ID TYPE   TABLE_NUM
------------------------------ ---------- ----- ----------
V$SESSION                      4294950919 VIEW       65537
V$DBLINK                       4294951157 VIEW       65537
 
SQL> 
SQL> COL OWNER  FOR A12;
SQL> COL OBJECT_NAME FOR A32;
SQL> COL OBJECT_TYPE FOR A32;
SQL> SELECT OWNER, OBJECT_NAME ,OBJECT_TYPE
  2  FROM DBA_OBJECTS 
  3  WHERE OBJECT_NAME='V$SESSION';
 
OWNER        OBJECT_NAME                      OBJECT_TYPE
------------ -------------------------------- --------------------------------
PUBLIC       V$SESSION                        SYNONYM
 
SQL> 
SQL> COL TABLE_OWNER FOR A12;
SQL> COL SYNONYM_NAME FOR A20;
SQL> COL TABLE_NAME FOR A16;
SQL> COL DB_LINK FOR A8;
SQL> SELECT * FROM DBA_SYNONYMS WHERE SYNONYM_NAME='V$SESSION';
 
OWNER        SYNONYM_NAME         TABLE_OWNER  TABLE_NAME       DB_LINK
------------ -------------------- ------------ ---------------- --------
PUBLIC       V$SESSION            SYS          V_$SESSION

所以要授权就应该执行下面 SQL 语句

SQL>
SQL> GRANT SELECT ON V_$SESSION TO TEST;
 
Grant succeeded.

如果遇到这样的错误,直接找到对应同义词对应的视图或基表,然后进行授权,如下所示:

SQL> show user;
USER is "SYS"
SQL> grant select on v$dblink to test;
grant select on v$dblink to test
                *
ERROR at line 1:
ORA-02030: can only select from fixed tables/views
 
 
SQL> COL OWNER  FOR A12;
SQL> COL OBJECT_NAME FOR A32;
SQL> COL OBJECT_TYPE FOR A32;
SQL> SELECT OWNER, OBJECT_NAME ,OBJECT_TYPE
  2  FROM DBA_OBJECTS 
  3  WHERE OBJECT_NAME=UPPER('v$dblink');
 
OWNER        OBJECT_NAME                      OBJECT_TYPE
------------ -------------------------------- --------------------------------
PUBLIC       V$DBLINK                         SYNONYM
 
SQL> COL TABLE_OWNER FOR A12;
SQL> COL SYNONYM_NAME FOR A20;
SQL> COL TABLE_NAME FOR A16;
SQL> COL DB_LINK FOR A8;
SQL> SELECT * FROM DBA_SYNONYMS WHERE SYNONYM_NAME='V$DBLINK';
 
OWNER        SYNONYM_NAME         TABLE_OWNER  TABLE_NAME       DB_LINK
------------ -------------------- ------------ ---------------- --------
PUBLIC       V$DBLINK             SYS          V_$DBLINK
 
SQL> grant select on v_$dblink to test;
 
Grant succeeded.
 
SQL> 

ORA-02030: can only select from fixed tables/views


以上所述就是小编给大家介绍的《ORA-02030: can only select from fixed tables/views》,希望对大家有所帮助,如果大家有任何疑问请给我留言,小编会及时回复大家的。在此也非常感谢大家对 码农网 的支持!

查看所有标签

猜你喜欢:

本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们

爱上Arduino

爱上Arduino

Massimo Banzi / 于欣龙、郭浩赟 / 人民邮电出版社 / 2012-10 / 38.00元

《硬件开源电子设计平台:爱上Arduino(第2版)》全面透彻地介绍了arduino的相关内容,它会给你带来许多项目的点子,并帮助你顺利地实现从开始策划直到完成安装的全过程。由于《硬件开源电子设计平台:爱上Arduino(第2版)》是arduino项目合作创始人massimobanzi所著,其中一定融入了创始人对开源硬件的独到见解。《硬件开源电子设计平台:爱上Arduino(第2版)》内容完全考虑......一起来看看 《爱上Arduino》 这本书的介绍吧!

JS 压缩/解压工具
JS 压缩/解压工具

在线压缩/解压 JS 代码

在线进制转换器
在线进制转换器

各进制数互转换器

Base64 编码/解码
Base64 编码/解码

Base64 编码/解码