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

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等拼接或函数的列名，必须派生字段别名。