求关于dba_free_space和dba_data_files的创建视图语句

[tulipcaicai]

我在新创建的用户下执行以下语句可以通过并得到结果

 select   a.tablespace_name,a.bytes/1024/1024 "Sum MB",(a.bytes-b.bytes)/1024/1024   "used MB",b.bytes/1024/1024 "free MB",round(((a.bytes-b.bytes)/a.bytes)*100,2) "percent_used"
from
(select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name)   a,
(select tablespace_name,sum(bytes) bytes,max(bytes) largest from dba_free_space group by tablespace_name)   b
where   a.tablespace_name=b.tablespace_name
order   by   ((a.bytes-b.bytes)/a.bytes)   desc 

但我执行创建视图语句却提示 ORA-01031:权限不足
创建视图语句如下：

create or replace view v_tssum as
 select   a.tablespace_name,a.bytes/1024/1024 "Sum MB",(a.bytes-b.bytes)/1024/1024   "used MB",b.bytes/1024/1024 "free MB",round(((a.bytes-b.bytes)/a.bytes)*100,2) "percent_used"
from
(select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name)   a,
(select tablespace_name,sum(bytes) bytes,max(bytes) largest from dba_free_space group by tablespace_name)   b
where   a.tablespace_name=b.tablespace_name
order   by   ((a.bytes-b.bytes)/a.bytes)   desc 

[tulipcaicai]

我赋予了用户创建视图的权限，而且用户还有DBA权限。同样的语句，我在system和sys下执行，都报权限不足。

[u010412956]

sys都不行？ 2个USER_NAME改成对面的用户名，看下啥结果

select privilege from dba_sys_privs where grantee='USER_NAME'
union 
select privilege from dba_sys_privs where grantee in (select granted_role from dba_role_privs where grantee='USER_NAME');

[tulipcaicai]

引用 4 楼 u010412956 的回复:

sys都不行？ 2个USER_NAME改成对面的用户名，看下啥结果

select privilege from dba_sys_privs where grantee='USER_NAME'
union 
select privilege from dba_sys_privs where grantee in (select granted_role from dba_role_privs where grantee='USER_NAME');

在system、和我创建的用户下，执行结果都是 0 行

[u010412956]

select privilege from dba_sys_privs where grantee='SYSTEM'
union 
select privilege from dba_sys_privs where grantee in (select granted_role from dba_role_privs where grantee='SYSTEM');

这个sql 会是0行？？？？？

[tulipcaicai]

引用 6 楼 u010412956 的回复:

select privilege from dba_sys_privs where grantee='SYSTEM'
union 
select privilege from dba_sys_privs where grantee in (select granted_role from dba_role_privs where grantee='SYSTEM');

这个sql 会是0行？？？？？

这个有好多行。我是说我执行这个语句（monitor是我创建的用户），用system和monitor登录后，执行该脚本结果为0行。

select privilege from dba_sys_privs where grantee='monitor'
union 
select privilege from dba_sys_privs where grantee in (select granted_role from dba_role_privs where grantee='monitor');

[wangchangming]

grant create view to 新建用户

[sych888]

赋予创建视图的权限

[u010412956]

引用 7 楼 tulipcaicai 的回复:

[quote=引用 6 楼 u010412956 的回复:]

select privilege from dba_sys_privs where grantee='SYSTEM'
union 
select privilege from dba_sys_privs where grantee in (select granted_role from dba_role_privs where grantee='SYSTEM');

这个sql 会是0行？？？？？

这个有好多行。我是说我执行这个语句（monitor是我创建的用户），用system和monitor登录后，执行该脚本结果为0行。

select privilege from dba_sys_privs where grantee='monitor'
union 
select privilege from dba_sys_privs where grantee in (select granted_role from dba_role_privs where grantee='monitor');

[/quote] monitor大写啊

[tulipcaicai]

引用 8 楼 u010412956 的回复:

monitor大写啊

用大写来查，果然有好多行，其中有“CREATE ANY VIEW”这一个结果

[tulipcaicai]

大家还有什么高招没？