Grant on V$ Views - ORA-02030

Today i was faced with an error when I try to give permission on a v$view

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

What is ORA-02030?

The error ORA-02030 comes up when an attempt is made to select from a database object that is not a table or view.

SQL> select object_name
  2       , owner
  3       , object_type
  4  from all_objects
  5  where object_name like 'V$INSTANCE';

OBJECT_NAME     OWNER    OBJECT_TYPE
--------------- -------- ----------------
V$INSTANCE      PUBLIC   SYNONYM

How to resolve ORA-02030

ORA-02030 can be eliminated by granting select on the underlying table/view of the v$ synonym.
To find the underlying table/view, query all_synonyms:

SQL> select table_owner, table_name from all_synonyms
  2  where synonym_name like 'V$INSTANCE';

TABLE_OWNER                    TABLE_NAME
------------------------------ -----------
SYS                            V_$INSTANCE

That’s it – synonym v$instance points to an object with a slightly different name: v_$instance.


v_$instance is, indeed, not a table but a view:

SQL> select object_type from all_objects where object_name = 'V_$INSTANCE';

OBJECT_TYPE
-------------------
VIEW

Given these findings, all that’s needed is to grant select to the non-admin user on the v_$ view corresponding to the v$ synonym.

SQL> grant select on v_$instance to hr;

Grant succeeded.