Oracle 查看表空间使用率

1.查询不包含临时表空间的使用率

SELECT Upper(F.TABLESPACE_NAME) "TablespaceName",
D.TOT_GROOTTE_MB "Total(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "Used(M)",
To_char(Round(( D.TOT_GROOTTE_MB - F.TOTAL_BYTES ) / D.TOT_GROOTTE_MB * 100, 2), '990.99')
|| '%' "Used%",
F.TOTAL_BYTES "Free(M)",
F.MAX_BYTES "max_block(M)"
FROM (SELECT TABLESPACE_NAME,
Round(Sum(BYTES) / ( 1024 * 1024 ), 2) TOTAL_BYTES,
Round(Max(BYTES) / ( 1024 * 1024 ), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
Round(Sum(DD.BYTES) / ( 1024 * 1024 ), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 1

 

2.查看临时表空间使用率

 

select c.tablespace_name,
to_char(c.bytes / 1024 / 1024, '99,999.999') total_bytes,
to_char((c.bytes - d.bytes_used) / 1024 / 1024, '99,999.999') free_bytes,
to_char(d.bytes_used / 1024 / 1024, '99,999.999') use_bytes,
to_char(d.bytes_used * 100 / c.bytes, '99.99') || '%' use
from (select tablespace_name, sum(bytes) bytes
from dba_temp_files
group by tablespace_name) c,
(select tablespace_name, sum(bytes_cached) bytes_used
from v$temp_extent_pool
group by tablespace_name) d
where c.tablespace_name = d.tablespace_name

 

3.查看临时表空间 用户使用情况

select username,tablespace,session_num sid,sqladdr,sqlhash,segtype,extents,blocks from v$sort_usage;