When querying v$instance or v$session[1] as a non-admin user, you might come across this error:
SQL> select version
2 from v$instance;
from v$instance
*
ERROR at line 2:
ORA-00942: table or view does not exist
The error suggests that the non-admin user does not have the SELECT privilege on the sys-owned v$ view. On the face of it, the fix appears as simple as: log in as sys and grant select on v$ views to the user.
Appearances are deceptive, they say. Try that and you will get an ORA-02030 error.
ORA-02030: can only select from fixed tables/views
SQL> grant select on v$instance
2 to hr;
grant select on v$instance
*
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.
Is that the case here? Is v$instance not a table or view? Let’s find out:
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
So…v$instance is a SYNONYM. It is not – as ORA-02030 rightly pointed out — a table or view.
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 2 , table_name 3 from all_synonyms 4 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.
The column table_name in all_synonyms is something of a misnomer. It is called table_name all right, but it need not always hold a table. The column table_name in all_synonyms can be any database object for which a synonym may be defined – view, sequence, etc. [Whoever wrote the story of A Table Is A Table would probably be astonished to see how close reality is to fiction.]
v_$instance is, indeed, not a table but a view:
SQL> select object_type 2 from all_objects 3 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 2 to hr; Grant succeeded.
Grant SUCCEEDED! Try selecting from V$ again…
Login as the non-admin user and retry the SQL that had failed earlier.
SQL> select version 2 from v$instance; VERSION ----------------- 11.2.0.2.0
It works.
Footnote: More about V$ views
[1] v$instance, v$session and other such v$ views are called dynamic performance views. They are continuously updated while a database is in use, and their contents relate primarily to performance.
These views provide data on internal disk/memory structures. You can select from v$ views, but you cannot update or alter them.
