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