Monday, January 10, 2011

How to grant on v$ views

When we  need to  grant on v$views  to a  user  I faced  with  ORA-02030 error

SQL> grant select on v$sqlarea to serdar;

grant select on v$sqlarea to serdar
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views

The  problem  is caused   because of trying to give select privilage on a synonym. Oracle v$ views are named V_$VIEWNAME and they have synonyms in format V$VIEWNAME and you can’t give privilage on a synonym

Try this 

SQL> grant select  on v_$sqlarea to serdar;
Grant succeeded.