从网上搜索过一些查询的sql语句,但是有些表在待查数据库中找不到,
如dba_data_files,这个表是用户权限控制访问的么?
另外,如dba_free_space也没有,可能需要用user_free_space来替换如
select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size
from dba_tablespaces t, dba_data_files d
where t.tablespace_name = d.tablespace_name
group by t.tablespace_name;请问有如何修改上述语句来查询阿?
或者有没有其他方法?
谢谢
如dba_data_files,这个表是用户权限控制访问的么?
另外,如dba_free_space也没有,可能需要用user_free_space来替换如
select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size
from dba_tablespaces t, dba_data_files d
where t.tablespace_name = d.tablespace_name
group by t.tablespace_name;请问有如何修改上述语句来查询阿?
或者有没有其他方法?
谢谢
dba_data_filesselect t.tablespace_name, round(nvl(u.used_size,0)*100/t.total_size,1)
from (select tablespace_name, sum(bytes) total_size
from dba_data_files
group by tablespace_name) t,
(select tablespace_name, sum(bytes) total_size
from dba_segments
group by tablespace_name) u
where t.tablespace_name=u.tablespace_name(+) and t.tablespace_name='your_ts_name';
trunc(a."total(M)") "TOTAL(M)",
nvl(trunc(b."remain(M)"), 0) "remain(M)",
nvl(trunc(b."remain(M)" / a."total(M)" * 100, 2), 0) || '%' "remain PERCENT(%)"
from (select tablespace_name, sum(bytes) / 1024 / 1024 "total(M)"
from dba_data_files
group by tablespace_name) a,
(select tablespace_name, sum(bytes) / 1024 / 1024 "remain(M)"
from dba_free_space
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name(+)
order by nvl(trunc(b."remain(M)"), 0) / a."total(M)"
请用dba用户查看。