查询表空间使用情况
SELECT UPPER(FTABLESPACE_NAME) 表空间名
DTOT_GROOTTE_MB 表空间大小(M)
DTOT_GROOTTE_MB FTOTAL_BYTES 已使用空间(M)
TO_CHAR(ROUND((DTOT_GROOTTE_MB FTOTAL_BYTES) / DTOT_GROOTTE_MB * )) || % 使用比
FTOTAL_BYTES 空闲空间(M)
FMAX_BYTES 最大块(M)
FROM (SELECT TABLESPACE_NAME
ROUND(SUM(BYTES) / ( * ) ) TOTAL_BYTES
ROUND(MAX(BYTES) / ( * ) ) MAX_BYTES
FROM SYSDBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F
(SELECT DDTABLESPACE_NAME
ROUND(SUM(DDBYTES) / ( * ) ) TOT_GROOTTE_MB
FROM SYSDBA_DATA_FILES DD
GROUP BY DDTABLESPACE_NAME) D
WHERE DTABLESPACE_NAME = FTABLESPACE_NAME
ORDER BY
查询表空间的free space
select tablespace_name
count(*) as extends
round(sum(bytes) / / ) as MB
sum(blocks) as blocks
from dba_free_space
group by tablespace_name;
查询表空间的总容量
select tablespace_name sum(bytes) / / as MB
from dba_data_files
group by tablespace_name;
查询表空间使用率
select totaltablespace_name
round(totalMB ) as Total_MB
round(totalMB freeMB ) as Used_MB
round(( freeMB / totalMB) * ) || % as Used_Pct
from (select tablespace_name sum(bytes) / / as MB
from dba_free_space
group by tablespace_name) free
(select tablespace_name sum(bytes) / / as MB
from dba_data_files
group by tablespace_name) total
where freetablespace_name = totaltablespace_name;
