欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-03-15 21:21:46
有时候给一些普通用户授予查询系统对象(例如dynamic performance views)权限时会遇到“ORA-02030: can only select from fixed tables/views”,如下所示: SQL> grant select on v$session to test... ......

有时候给一些普通用户授予查询系统对象(例如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>