There were situations
when the application team asks for access to dynamics views such as V$SESSION or
similar dynamic views. Access on V$SESSION is required in
order to monitor sessions from the application end, so this is a common request
Here, we will discuss, granting access to dynamic views, if you try to grant access to these dynamic views, it will throw ORA error ORA-02030 as snipped below
SQL> grant select on v$session to tech_owner;
grant select on v$session to
tech_owner
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views
SQL>
The meaning of this
error is, we can assign select access to tables and views only. We will see the
object type of this V$SESSION dynamic view
[Also Read - Oracle SCN]
SQL> select owner, object_name, object_type from dba_objects where object_Name='V$SESSION';
OWNER OBJECT_NAME OBJECT_TYPE
------------ --------------------
-------------------
PUBLIC
V$SESSION SYNONYM
We can see, V$SESSION is neither view nor table, now we will see base object of this synonym
SQL> select owner, synonym_name, table_owner,
2 table_name from
dba_synonyms where synonym_name='V$SESSION';
OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME
----------
---------------- ------------------- ---------------------
PUBLIC V$SESSION SYS V_$SESSION
here we can see the base object for synonym V$SESSION is V_$SESSION, now we will see the type of this object V_$SESSION
SQL> select owner, object_name, object_type from
dba_objects where object_Name='V_$SESSION';
OWNER OBJECT_NAME
OBJECT_TYPE
------------ --------------------
-------------------
SYS
V_$SESSION VIEW
We can see V_$SESSION is a view so we can grant access on V_$SESSION to get access on V$SESSION dynamic view
SQL> grant select on v_$session to
tech_owner;
Grant succeeded.
