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