Skip to content

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

  • by

ORA-02030

Tried to grant SELECT object privilege to an user, but it failed with ORA-02030.

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

ORA-02030 means that the type of object you want to grant SELECT privilege to other users cannot not be done, you should find the right object to be granted.

SYNONYM

Let's see what object type of it.

SQL> select * from all_objects where object_type in ('TABLE', 'VIEW') and object_name = 'V$SESSION';

no rows selected

OK, then what is the object type of V$SESSION.

SQL> select object_type from all_objects where object_name = 'V$SESSION';

OBJECT_TYPE
-----------------------
SYNONYM

Oh, it's a synonym. Type SYNONYM is just an alias of another object, it cannot be granted with SELECT privilege, only TABLE and VIEW can.

Now, the question is, what is the base object of this synonym? Let's continue.

TABLE or VIEW

Let's check the underlying object by querying ALL_SYNONYMS.

SQL> select table_name from all_synonyms where synonym_name = 'V$SESSION';

TABLE_NAME
-----------------------
V_$SESSION

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

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

OK, the base object should be V_$SESSION and it's a view, now we can grant the correct object to the user.

SQL> grant select on v_$session to erpapp;

Grant succeeded.

We made it.

Not only V$SESSION, but also V$SQL and V$DATABASE can not be granted with SELECT privilege directly, we should find the right object type to do it.

Leave a Reply

Your email address will not be published.