scott@ORCL> 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;
from v$statname a, v$mystat b
*
第 3 行出现错误:
ORA-01031: 权限不足;

SQL> create role pub_role;

Role created

SQL> grant select on v$statname to pub_role;

grant select on v$statname to pub_role

ORA-02030: 只能从固定的表/视图查询

SQL> grant select on V_$MYSTAT to pub_role;

Grant succeeded

SQL> grant select on v_$latch to pub_role;

Grant succeeded

SQL> grant select on V_$TIMER to pub_role;

Grant succeeded

scott@ORCL> select 'STAT...' || a.name name, b.value
2 from v$statname a, v$mystat b
3 where a.statistic# = b.statistic#
4 union all
5 select 'LATCH.' || name, gets
6 from v$latch;

NAME VALUE
----------------------------------------------------------------------- ----------
STAT...logons cumulative 1

scott@ORCL> 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;
from v$statname a, v$mystat b
*
第 3 行出现错误:
ORA-01031: 权限不足

------------竟然还是01031错误。

SQL> grant select on v_$statname to u_omar;

Grant succeeded

SQL> grant select on V_$MYSTAT to u_omar;

Grant succeeded

scott@ORCL> 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;

视图已创建。

----视图和存储过程一样。需要直接授权，通过角色获得的权限无效----------

---对动态性能表赋予权限的时候要 v$session ==>v_$session