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

情形：创建一个oracle用户testuser,并赋予v$session视图的查询权限。

SQL> conn / as sysdba
Connected.
SQL> create user testuser identified by testuser;

User created.

SQL> grant connect,resource to testuser;

Grant succeeded.

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

SQL>
报错了！感觉不可能啊。查了下oracle官方文档：

ORA-02030: 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.

没看懂什么意思，在网上查了好多资料，终于明白官方文档的意思了。

下面是我解决该错误，所涉及的知识点。

1、Static Data Dictionary Views

They are called static, because they change only when a change is made to the data dictionary (for example, when a new table is created or when a user is granted new privileges).

Data dictionary tables are not directly accessible, but you can access information in them through data dictionary views. To list the data dictionary views available to you, query the view DICTIONARY.

Many data dictionary tables have three corresponding views:

(1) USER_XXX : displays all the information from the schema of the current user. No special privileges are required to query these views.

(2) ALL_XXX : displays all the information accessible to the current user, including information from the current user's schema as well as information from objects in other schemas, if the current user has access to those objects by way of grants of privileges or roles.

(3) DBA_XXX : displays all relevant information in the entire database. DBA_ views are intended only for administrators. They can be accessed only by users with the SELECT ANY TABLE privilege. This privilege is assigned to the DBA role when the system is initially installed.

2、Dynamic Performance Views

Oracle contains a set of underlying views that are maintained by the database server and accessible to the database administrator user SYS. These views are called dynamic performance views because they are continuously updated while a database is open and in use, and their contents relate primarily to performance.

Although these views appear to be regular database tables, they are not. These views provide data on internal disk structures and memory structures. You can select from these views, but you can never update or alter them.The catalog.sql script contains definitions of the views and public synonyms for the dynamic performance views. You must run catalog.sql to create these views and synonyms. After installation, only user SYS or anyone with SYSDBA role has access to the dynamic performance tables.

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.

大家看到这里，可能还是不够理解上面的赋权限语句为什么错误？

下面我就用大白话的形式说一下我的理解哇。

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

这里的意思是: 对于fixed tables/views 只能查询，不能做其他操作，例如GRANT SELECT ON V$SESSION TO testuser;

利用V$FIXED_TABLE来查询数据库都有哪些fixed tables/views.

V$FIXED_TABLE : This view displays all dynamic performance tables, views, and derived tables in the database. Some V$ tables (for example, V$ROLLNAME) refer to real tables and are therefore not listed.

可以利用V$FIXED_VIEW_DEFINITION来查询某一个fixed table/view的定义。

V$FIXED_VIEW_DEFINITION ： This view contains the definitions of all the fixed views

3、fixed tables/views只可以执行select或desc

4、fixed tables/views只在v$fixed_table存在

5、非sys用户如何访问fixed tables/views (这里以v$session为例)

方法一：granting SELECT privilege to testuser on V_$SESSION

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

SQL> grant select on V_$SESSION to testuser;

Grant succeeded.

SQL> conn testuser/testuser
Connected.
SQL> select count(*) from v$session;

  COUNT(*)
----------
        30

方法二：creating a view based on V$SESSION and granting SELECT privilege to testuser on the view that was created

SQL> conn / as sysdba
Connected.
SQL> create view db_session as select * from v$session;

View created.

SQL> grant select on db_session to testuser;

Grant succeeded.

SQL> conn testuser/testuser
Connected.
SQL> select count(*) from v$session;

  COUNT(*)
----------
        30

SQL>