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

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