今天在学习tom老师的oracle 编程的时候出现了错误,就错在创建视图
- 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
- union all
- select 'STAT...Elapsed Time', hsecs from v$timer;
总结一下,出现错误的原因有两个
1. 具有dba角色的用户可以创建视图也可以查看其它视图,但是如果该用户想要基于视图B创建一个视图A的话,那么必须显示的为该用户授予select 该视图的权限,否则就会出现 "ORA-01031: insufficient privileges"
reason:Under SQL, if a user can select another user's table and has the privilege to create a view, then the create view works. Yet, a create view on the other user's table generates ORA-01031 if the select privilege has been granted to a role and not directly.
2. ok, 这一步就是解决如何授权的问题
如果你直接执行 grant select on v$statname to xxxx , 那么一定会有一个ora-02030在等着你
正确的做法是 grant select on v_$statname to xxxx
why?
因为 v$statname 只是 v_$statname 的同义词
reference:
阅读(1668) | 评论(0) | 转发(0) |