最近在看Thomas Kyte的<Expert Oracle.Database.Architecture.9i.and.10g>,在定制环境的时候，运行以下脚本，出现问题： scott@SHENZHEN> grant select any table to scott; scott@SHENZHEN> create or replace view stats
as select 'STAT...' || a.name name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
union all
select 'LATCH.' || name, gets
from v$latchlatch
union all
9 select 'STAT...Elapsed Time', hsecs from v$timer;
from v$statname a, v$mystat b
*
ERROR at line 3:
ORA-01031: insufficient privileges
[@more@]
欲将这几个动态性能视图的select权限单独赋给Scott：
sys@SHENZHEN> grant select on v$mystat to scott;
grant select on v$mystat to scott
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views
查询后发现，原来v$对象，并不是视图，而是指向v_$视图的同义词，而视图是基于真正的v$视图创建的。
还有X$表，这一部分表是Oracle数据库的运行基础，在数据库启动时由Oracle应用程序动态创建。
这部分表对数据库来说至关重要，所以Oracle不允许SYSDBA之外的用户直接访问，显示授权不被允许。
将select权限赋给4个v_$视图
sys@SHENZHEN> grant select on v_$mystat to scott;
Grant succeeded.
sys@SHENZHEN> grant select on v_$statname to scott;
Grant succeeded.
sys@SHENZHEN> grant select on v_$latch to scott;
Grant succeeded.
sys@SHENZHEN> grant select on v_$timer to scott;
Grant succeeded.
scott@SHENZHEN> create or replace view stats
2 as select 'STAT...' || a.name name, b.value
3 from v$statname a, v$mystat b
4 where a.statistic# = b.statistic#
5 union all
6 select 'LATCH.' || name, gets
7 from v$latch
8 union all
9 select 'STAT...Elapsed Time', hsecs from v$timer;
View created.
问题解决！
Reference:
http://space.itpub.net/9252210/viewspace-591741
http://www.orafaq.com/forum/t/72902/2/
http://zhangsolomon.itpub.net/post/39913/488842
http://blog.csdn.net/yang073402/archive/2010/04/13/5482987.aspx
http://www.alidw.com/?p=263
