Wednesday, September 17, 2014

ORA-02030: can only select from fixed tables/views Error While Granting Access To Oracle V$ Views

This is a pretty common mistake to make when we are learning Oracle database. Basically when we select the data from all these dynamic performance views like V$SESSION or V$TRANSACTION etc., we are using the public synonyms. So if you try to grant select access on these synonyms like below, you will get "ORA-02030: can only select from fixed tables/views" error.

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

So you need to grant select permission on the underlying views, not on synonyms. That's pretty straightforward as Oracle has a common naming convention for these v$ views. Just add a underscore "_" after "V" like V_$SESSION or V_$TRANSACTION. So if you just change the above SQL statement like below, you will scceed.

SQL> grant select on v_$transaction to test;
Grant succeeded.

No comments:

Post a Comment