表空间碎片相关：
select tablespace_name,
round(sqrt(max(blocks) / sum(blocks)) *
(100 / sqrt(sqrt(count(blocks)))),
2) FSFI,
(case
when sqrt(max(blocks) / sum(blocks)) *
(100 / sqrt(sqrt(count(blocks)))) > = 30 then
'正常'
when sqrt(max(blocks) / sum(blocks)) *
(100 / sqrt(sqrt(count(blocks)))) < 30 then
'表空间破碎化程度高，请整理'
end) Prompt
from dba_free_space
group by tablespace_name
order by 2;
--排除SYSAUX和UNDOTBS1
整理碎片： alter tablespace coalesce;
查看单个数据文件使用情况：
select distinct d.name,
t.name,
round(s.bytes / (1024 * 1024), 0) total_space,
round(s.bytes / (1024 * 1024), 0)-round(f.bytes / (1024 * 1024), 0) used
from v$tablespace t, v$datafile d, dba_data_files s, dba_free_space f
where t.ts# = d.ts#
and d.name = s.file_name
and s.file_id = f.file_id
and d.file# = f.file_id
and t.name = f.tablespace_name
表空间使用率：
SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
D.TOT_GROOTTE_MB "表空间大小(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100, 2),'990.99') || '%' "使用比(%)",
F.TOTAL_BYTES "空闲空间(M)",
F.MAX_BYTES "最大块(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 4 desc;
查看某个表空间实际剩余空间sql：
select （(
nvl((select sum(t.maxblocks*8/1024) from dba_data_files t where t.autoextensible='YES'and t.tablespace_name = 'SYSTEM'),0)
+
nvl((select sum(t.blocks*8/1024) from dba_data_files t where t.autoextensible='NO'and t.tablespace_name = 'SYSTEM'),0))
-
（
SELECT D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(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 AND D.TABLESPACE_NAME = 'SYSTEM'
）） "实际剩余空间(M)"
from dual;
数据文件使用率：
select tot.file_name,100*(tot.total-free.total)/tot.total||'%' from
(select file_name,file_id,sum(bytes) as total from dba_data_files tot group by file_id,file_name ) tot,
(select file_id,sum(bytes) as total from dba_free_space free group by file_id) free
where tot.file_id=free.file_id
经测试，查看数据文件使用率，性能较好，较准确的：
SELECT B.TABLESPACE_NAME,
B.FILE_NAME,
ROUND (B.BYTES / 1024 / 1024 / 1024, 2) "SIZE GB",
ROUND (B.MAXBYTES / 1024 / 1024 / 1024, 2) "MAX SIZE GB",
ROUND (SUM (A.BYTES) / 1024 / 1024 / 1024, 2) "FREE SPACE GB",
CASE
WHEN MAXBYTES = 0
THEN
ROUND (SUM (A.BYTES) / 1024 / 1024 / 1024, 2)
ELSE
ROUND (
( (B.MAXBYTES - B.BYTES) + SUM (A.BYTES)) / 1024 / 1024 / 1024, 2)
END
"TOTAL FREE SPACE GB"
FROM DBA_FREE_SPACE A, DBA_DATA_FILES B
WHERE A.FILE_ID = B.FILE_ID AND B.TABLESPACE_NAME IN ('CBAM')
GROUP BY B.TABLESPACE_NAME,
B.FILE_NAME,
B.BYTES,
B.MAXBYTES
ORDER BY 1, 2;
查看TEMP表空间使用情况：
SELECT a.tablespace_name,
a.BYTES / 1024 / 1024 "total(M)",
(a.bytes - nvl(b.bytes, 0)) / 1024 / 1024 "free(M)"
FROM (SELECT tablespace_name, SUM(bytes) bytes
FROM dba_temp_files
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes_cached) bytes
FROM v$temp_extent_pool
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name(+)