查看oracle空间使用率,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

标签：1024,name,查看,BYTES,bytes,TABLESPACE,Oracle,tablespace,使用率

来源： https://www.cnblogs.com/zhm1985/p/11958303.html