Grant for Selecting V$ Performance Views To a User

By default, it is not possible to query v$ tables(like v$session) unless you have sysdba role. If you want to give select right to a user, you can explicitly grant that table( like grant select on v$sql to someone). If you want to give all select rights, you can give SELECT_CATALOG_ROLE to that user. It is not recommended to give this role, but if necessary you can grant role.

Connected to Oracle Database 11g Express Edition Release 11.2.0.2.0 

Connected as hr@xE.LOCAL

 

SQL> select * from v$sql;

 

select * from v$sql

 

ORA-00942: table or view does not exist

 

SQL> conn sys@xe.local as sysdba

Connected to Oracle Database 11g Express Edition Release 11.2.0.2.0 

Connected as sys@XE.LOCAL AS SYSDBA

 

SQL> grant SELECT_CATALOG_ROLE to hr;

 

Grant succeeded

 

SQL> conn hr/hr@xe.local

Connected to Oracle Database 11g Express Edition Release 11.2.0.2.0 

Connected as hr@xE.LOCAL

 

SQL> select * from v$sql;

 

SQL_TEXT   .......