create view过程中遇到的ora-01031、ORA-00998错误

最新推荐文章于 2022-12-09 10:30:29 发布

原创最新推荐文章于 2022-12-09 10:30:29 发布 · 9.7k 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

标签

#timer #sql #nosql #table #user

ORACLE 专栏收录该内容

42 篇文章

订阅专栏

SQL> --创建视图
SQL> create or replace view stats
SQL> as
SQL>   select 'STAT...'||a.name,b.value
SQL>   from v$statname a,v$mystat b
SQL>   where a.STATISTIC#=b.STATISTIC#
SQL>   union all
SQL>   select 'LATCH.'||name,gets
SQL>   from v$latch
SQL>   union all
SQL>   select 'STAT...Elapsed Time',hsecs
SQL>   from v$timer;

create or replace view stats
as
select 'STAT...'||a.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;
ora-01031:insufficient privileges

SQL> select * from user_tab_privs_recd;

OWNER                          TABLE_NAME                     GRANTOR                        PRIVILEGE                                GRANTABLE HIERARCHY
------------------------------ ------------------------------ ------------------------------ ---------------------------------------- --------- ---------
SYS                            V_$LATCH                       SYS                            SELECT                                   NO        NO
SYS                            V_$STATNAME                    SYS                            SELECT                                   NO        NO
SYS                            V_$TIMER                       SYS                            SELECT                                   NO        NO

SQL> --根据查询结果可知用户没有v_$mystat的select权限，故
SQL> grant select on v_$mystat to scott;

SQL> --创建视图
SQL> create or replace view stats
SQL> as
SQL>   select 'STAT...'||a.name,b.value
SQL>   from v$statname a,v$mystat b
SQL>   where a.STATISTIC#=b.STATISTIC#
SQL>   union all
SQL>   select 'LATCH.'||name,gets
SQL>   from v$latch
SQL>   union all
SQL>   select 'STAT...Elapsed Time',hsecs
SQL>   from v$timer;

create or replace view stats
as
select 'STAT...'||a.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;
ORA-00998: must name this expression with a column alias

SQL> --修改视图,为'STAT...'||a.name as一个别名
SQL> create or replace view stats
SQL> as
SQL>   select 'STAT...'||a.name as name,b.value
SQL>   from v$statname a,v$mystat b
SQL>   where a.STATISTIC#=b.STATISTIC#
SQL>   union all
SQL>   select 'LATCH.'||name,gets
SQL>   from v$latch
SQL>   union all
SQL>   select 'STAT...Elapsed Time',hsecs
SQL>   from v$timer;

Create succeeded

总结：1.v$statname、v$mystat、v$latch和v$timer分别是对象v_$statname、v_$mystat、v_$latch和v_$timer的同义词，因此创建视图时需要sys用户直接授予用户select的权限，即创建视图时对象的权限不可继承；
2.创建视图时，类似'STAT...'||a.name等拼接或函数的列名，必须派生字段别名。