点击上方“IT那活儿”公众号，关注后了解更多内容，不管IT什么活儿，干就完了！！！
知识点描述
在Oracle数据库中表随着数据增长，所占磁盘容量也会增长，当表中数据不再需要时，对表进行delete操作，表中代表所占空间的标志-高水位，不会随着数据删除而下降，高水位没有变化，即使删除了数据，表所占空间大小仍然不变。
标准指导操作
2.1 查看表空间使用情况
SET LINESIZE 500
SET PAGESIZE 1000
col FREE_SPACE(M) for 999999999
col USED_SPACE(M) for 999999999
col TABLESPACE_NAME for a40
SELECT D.TABLESPACE_NAME,SPACE "SUM_SPACE(M)",SPACE - NVL(FREE_SPACE, 0) "USED_SPACE(M)",
ROUND((1 - NVL(FREE_SPACE, 0) SPACE) * 100, 2) "USED_RATE(%)", FREE_SPACE "FREE_SPACE(M)",
case when FREE_SPACE=REA_FREE_SPACE then null else ROUND((1 - NVL(REA_FREE_SPACE, 0) / SPACE) * 100, 2) end "REA_USED_RATE(%)",
case when FREE_SPACE=REA_FREE_SPACE then null else REA_FREE_SPACE end "REA_FREE_SPACE(M)"
FROM
(SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) D,
(
SELECT F1.TABLESPACE_NAME, F1.FREE_SPACE-NVL(F2.FREE_SPACE,0) REA_FREE_SPACE,F1.FREE_SPACE
FROM
(
SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / (1024 * 1024), 2) FREE_SPACE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME
) F1,
(
SELECT TS_NAME TABLESPACE_NAME, ROUND(SUM(SPACE)*8/1024,2) FREE_SPACE FROM DBA_RECYCLEBIN GROUP BY TS_NAME
) F2
WHERE F1.TABLESPACE_NAME=F2.TABLESPACE_NAME(+)
) F
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
ORDER BY 1 - NVL(REA_FREE_SPACE, 0) / SPACE DESC;
2.2 查看包含lob字段的表空间的大对象
set pages 10000 linesize 300
col owner for a20
col tablespace_name for a20
col segment_type for a20
col segment_name for a45
col PARTITION_NAME for a30
select * from (
select owner, tablespace_name,segment_type, segment_name,PARTITION_NAME, sum(bytes) / 1024 / 1024 /1024 size_G
from dba_segments
where tablespace_name in (upper('tablespace_name'))
group by owner, tablespace_name,segment_type, segment_name,PARTITION_NAME
order by 6 desc) where rownum
