这个视图创建不成功create or replace view test 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: 权限不足create or replace view test as select * from t;可以创建成功.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
也能正常查出数据,各位能否帮忙看看是什么原因,不胜感激.
from v$statname a,v$mystat b where a.statistic# = b.STATISTIC#
union all
select 'LATCH.'|| name,gets from v$latch错误提示:ORA-01031: 权限不足create or replace view test as select * from t;可以创建成功.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
也能正常查出数据,各位能否帮忙看看是什么原因,不胜感激.
USER 为 "TEST"
SQL> select * from user_role_privs;USERNAME GRANTED_ROLE ADM DEF OS_
------------------------------ ------------------------------ --- --- ---
TEST CONNECT NO YES NO
TEST DBA NO YES NO
TEST RESOURCE NO YES NOSQL> --角色不起作用,需要显示授权
SQL> CREATE OR REPLACE view test 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 FROM v$latch;
FROM v$statname a, v$mystat b
*
第 3 行出现错误:
ORA-01031: 权限不足
SQL> connect sys/[email protected] as sysdba;
已连接。
SQL> --显示授权
SQL> grant select any dictionary to test;授权成功。SQL> connect test/[email protected]
已连接。
SQL> CREATE OR REPLACE view test 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 FROM v$latch;视图已创建。
--普通用户是没有权限查询这两个视图的,
--你可以conn as sysdba,然后授权查询所有数据字典视图给当前用户
--也可以在dba身份下建立你的这个视图,然后授权给当前用户查询此视图的权限。
SQL> show user;
USER is "SCOTT"SQL> desc v$statname;
ERROR:
ORA-04043: object "SYS"."V_$STATNAME" does not existSQL> desc v$mystat
ERROR:
ORA-04043: object "SYS"."V_$MYSTAT" does not existConnected as SYS
SQL> create or replace view test 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 from v$latch;
View created