动态视图赋权限

ORA-02030: can only select from fixed tables/views

阅读tom的书时，实验环境需要建立一个收集状态信息的视图。

xezf@SQL> create or replace view stats as
2 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 /

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$latch

ORA-01031: insufficient privileges

尝试给用户xezf赋权限

sys@SQL> grant select on v$statname to xezf;
grant select on v$statname to xezf
                *
ERROR at line 1:
ORA-02030: can only select from fixed tables/views

[xezf@dbtest ~]$ oerr ora 2030
02030, 00000, "can only select from fixed tables/views"
// *Cause: An attempt is being made to perform an operation other than
//         a retrieval from a fixed table/view.
// *Action: You may only select rows from fixed tables/views.

对于fixed tables/view 只能检索数据，不能进行其他的操s@作。怎么办呢？

我们知道v$是v_$的同义词，试试对v_$赋权限怎么样？

sys@SQL> grant select on v_$statname to xezf;

Grant succeeded.

sys@SQL> grant select on v_$mystat to xezf;

Grant succeeded.

sys@SQL> grant select on v_$latch to xezf;

Grant succeeded.

OK，可以grant。 再尝试建立视图。

xezf@SQL> create or replace view stats as
2 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 ;

View created