select a.tablespace_name, a.tablespace_size * b.block_size / 1024 / 1024 "totalmsize(m)", a.used_space * b.block_size / 1024 / 1024 "usedmsize(m)", round(a.used_percent, 2) "usedrate(%)" from dba_tablespace_usage_metrics a, dba_tablespaces b where a.tablespace_name = b.tablespace_name;
-- *01). 查看普通表空间的使用情况: SELECT a.tablespace_name as tablespace_name, to_char(b.total/1024/1024,999999.99) as Total, to_char((b.total-a.free)/1024/1024,'9999990D99') as Used, to_char(a.free/1024/1024,'9999990D99') as Free, to_char(round((total-free)/total,4)*100,'9999990D99')||'%' as Used_Rate FROM (SELECT tablespace_name, sum(bytes) free FROM DBA_FREE_SPACE GROUP BY tablespace_name) a, (SELECT tablespace_name, sum(bytes) total FROM DBA_DATA_FILES GROUP BY tablespace_name ) b WHERE a.tablespace_name=b.tablespace_name ORDER BY round((total-free)/total,4) DESC;-- *02). 查看临时表空间的使用情况: su - oracle sqlplus /nolog conn / as sysdba SELECT temp_used.tablespace_name, total - used as "Free", total as "Total", round(nvl(total - used, 0) * 100 / total, 3) "Free percent" FROM (SELECT tablespace_name, SUM(bytes_used)/1024/1024 used FROM GV_$TEMP_SPACE_HEADER GROUP BY tablespace_name) temp_used, (SELECT tablespace_name, SUM(bytes)/1024/1024 total FROM dba_temp_files GROUP BY tablespace_name) temp_total WHERE temp_used.tablespace_name = temp_total.tablespace_name;
-- 总容量,就是一个sum()函数求和,如果这个也不会的话,你可以回家种田啦!
我需要的是没有SYSDBA权限下的统计,不懂吗????
问题已经自己解决了,现在将解决方法贴出来以供参考 下面是查询语句:select a.bytes/1024/1024 "used(MB)", b.bytes/1024/1024 "free(MB)", a.bytes/1024/1024+b.bytes/1024/1024 "total(MB)" from user_ts_quotas a,user_free_space b where a.TABLESPACE_NAME=b.TABLESPACE_NAME and a.TABLESPACE_NAME='SSPATBS';
select a.tablespace_name,
a.tablespace_size * b.block_size / 1024 / 1024 "totalmsize(m)",
a.used_space * b.block_size / 1024 / 1024 "usedmsize(m)",
round(a.used_percent, 2) "usedrate(%)"
from dba_tablespace_usage_metrics a, dba_tablespaces b
where a.tablespace_name = b.tablespace_name;
SELECT a.tablespace_name as tablespace_name,
to_char(b.total/1024/1024,999999.99) as Total,
to_char((b.total-a.free)/1024/1024,'9999990D99') as Used,
to_char(a.free/1024/1024,'9999990D99') as Free,
to_char(round((total-free)/total,4)*100,'9999990D99')||'%' as Used_Rate
FROM (SELECT tablespace_name, sum(bytes) free FROM DBA_FREE_SPACE GROUP BY tablespace_name) a,
(SELECT tablespace_name, sum(bytes) total FROM DBA_DATA_FILES GROUP BY tablespace_name ) b
WHERE a.tablespace_name=b.tablespace_name
ORDER BY round((total-free)/total,4) DESC;-- *02). 查看临时表空间的使用情况:
su - oracle
sqlplus /nolog
conn / as sysdba
SELECT temp_used.tablespace_name,
total - used as "Free",
total as "Total",
round(nvl(total - used, 0) * 100 / total, 3) "Free percent"
FROM (SELECT tablespace_name, SUM(bytes_used)/1024/1024 used FROM GV_$TEMP_SPACE_HEADER GROUP BY tablespace_name) temp_used,
(SELECT tablespace_name, SUM(bytes)/1024/1024 total FROM dba_temp_files GROUP BY tablespace_name) temp_total
WHERE temp_used.tablespace_name = temp_total.tablespace_name;
-- 总容量,就是一个sum()函数求和,如果这个也不会的话,你可以回家种田啦!
下面是查询语句:select
a.bytes/1024/1024 "used(MB)",
b.bytes/1024/1024 "free(MB)",
a.bytes/1024/1024+b.bytes/1024/1024 "total(MB)"
from user_ts_quotas a,user_free_space b
where a.TABLESPACE_NAME=b.TABLESPACE_NAME and a.TABLESPACE_NAME='SSPATBS';