When you grant v$session on select to normal user,you will receive:
ORA-02030: can only select from fixed tables/views
e.g.
SQL > conn sys/oracle@devdb1 as sysdba
Connected.
SQL> create user leiz identified by leiz default tablespace data01;
User created.
SQL> grant connect, resource,create synonym to leiz;
Grant succeeded.
SQL> grant select on v$session to leiz;
grant select on v$session to leiz
ERROR at line 1:
ORA-02030: can only select from fixed tables/views
Reason:
The actual view created on the dynamic performance table is named V_$(view name). The V$(view name) is only a synonym and not an actual fixed table or a view.
SQL> select OWNER, OBJECT_TYPE,OBJECT_NAME
from dba_objects
where object_name in ('V$SESSION','V_$SESSION');
OWNER OBJECT_TYPE OBJECT_NAME
---------- -------------------- --------------------
SYS VIEW V_$SESSION
PUBLIC SYNONYM V$SESSION
SQL> select * from v$fixed_view_definition c
e.g.
SQL > conn sys/oracle@devdb1 as sysdba
Connected.
SQL> create user leiz identified by leiz default tablespace data01;
User created.
SQL> grant connect, resource,create synonym to leiz;
Grant succeeded.
SQL> grant select on v$session to leiz;
grant select on v$session to leiz
ERROR at line 1:
ORA-02030: can only select from fixed tables/views
Reason:
The actual view created on the dynamic performance table is named V_$(view name). The V$(view name) is only a synonym and not an actual fixed table or a view.
SQL> select OWNER, OBJECT_TYPE,OBJECT_NAME
from dba_objects
where object_name in ('V$SESSION','V_$SESSION');
OWNER OBJECT_TYPE OBJECT_NAME
---------- -------------------- --------------------
SYS VIEW V_$SESSION
PUBLIC SYNONYM V$SESSION
SQL> select * from v$fixed_view_definition c
Solution:
Granting select on the underlying V_$ view instead of the V$ synonym will allow you to grant select on the object.
SQL>grant select on v_$session to leiz;
Grant succeeded.(sys user)
SQL> connleiz/leiz@devdb1
Connected.
SQL> select count(*) from sys.v_$session;
COUNT(*)
----------
33
SQL> create synonym v$session for sys.v_$session;
Synonym created.
SQL> select count(*) from v$session;
COUNT(*)
----------
33