认真就输

"Checked for relevance on 24 October 2007"

Problem Description:  
====================  

When trying to grant select on a v$ view as sys, to a user that is not able to 
select on those views, you receive the following error: 
 
   ORA-02030: can only select from fixed tables/views when granting 
   select on a v$view.   
 
   Error:  ORA 2030 
   Text:   can only select from fixed tables/views 
   Cause:  An operation other than SELECT on a fixed dynamic performance 
           table or view was attempted. 
   Action: Remove the fixed table or view name from the SELECT statement. 
 
You can select on the view but cannot grant any object privileges on the view.


Solution Description:  
======================  

Granting select on the underlying V_$ view instead of the V$ synonym will 
allow you to grant select on the object.  
 
Example: 
 
    grant select on v_$database to scott;   
 
  
Explanation:  
============
  
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.  Thus, when you try to grant select on the object you will receive the 
error:  ORA-02030: can only select from fixed tables/views.
Grant Select on V_$DATABASE

For Example:
-------------

SVRMGR> grant select on v$database to scott;
grant select on v$database to scott
                *
ORA-02030: can only select from fixed tables/views

SVRMGR> select OWNER, OBJECT_TYPE,OBJECT_NAME from dba_objects
        where object_name='V$DATABASE';


OWNER    OBJECT_TYPE   OBJECT_NAME
------   -----------   -----------
PUBLIC   SYNONYM       V$DATABASE

Note:  This tells us that V$DATABASE is just a synonym.

SVRMGR> select SYNONYM_NAME,TABLE_OWNER,TABLE_NAME from dba_synonyms
        where synonym_name='V$DATABASE';

SYNONYM_NAME   TABLE_OWNER    TABLE_NAME
------------   -----------    ----------
V$DATABASE     SYS         V_$DATABASE

Note:  This tells us the V$DATABASE is synonym for SYS.V_$DATABASE,
       so we need to grant select on the v_$database.

SVRMGR> grant select on v_$database to scott;
Statement processed.


Search Words:
=============

ORA-2030