关于Oracle大字段表空间的清理思路

最近发现生产库中有一个临时的大字段缓存表增长的非常厉害，几乎两天就需要消耗掉30G的容量，这样下去就算是磁盘容量再大没几天也要撑爆。
所以想到定期清理，因为含有blob字段，清理方式略有不同，以下是我清理大字段的思路和方法。

1.查询表空间使用情况，定位问题表空间。
查询发现表空间sa****已经使用了125G，使用率已经高达92%了。

SELECT a.tablespace_name “tb_name”,
total / (1024 * 1024 * 1024) “tb_sizeG)”,
free / (1024 * 1024 * 1024) “tb_free_size(G)”,
(total - free) / (1024 * 1024 * 1024) “tb_used_size(G)”,
round((total - free) / total, 4) * 100 “tb_usage %”
FROM (SELECT tablespace_name, SUM(bytes) free
FROM dba_free_space
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) total
FROM dba_data_files
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name;

2.查看空间占用超过1G（可以根据实际情况调整）的字段
查询发现syslob********这个大字段占用了将近90G的空间。
*上面还有一个syslob大字段的空间占用了60G这个是我们长期储存大字段数 据用的，不能清理，所以直接忽略。

select segment_name,sum(bytes)/1024/1024 from dba_segments group by segment_name having sum(bytes)/1024/1024>1000;

3.定位大字段所在的表名,表空间名，定位到该表是loadtab,是一张临时缓存表，表空间也确实是SA表空间。

select table_name,tablespace_name,segment_name from dba_lobs where segment_name=‘SYS_***’;

4.确认需要清理的表loading*的索引。
因为大字段的清理需要move表空间，会造成索引失效，所以清理完成后需要重建索引。
通过查询发现索引为LOADINGPK。

select table_name,index_name,status from dba_indexes where table_name=‘LOADING***TAB’;

5.接下来就可以清理，根据自身需求清理。
我是清理七天前的数据。

DELETE FROM loadingtab WHERE exists(
SELECT
loadingid
FROM
loading***tab
WHERE
state_date < ( SYSDATE - 7 )
);

6.再次查询表空间使用情况，发现表空间没有释放，还是92%的使用率。

SELECT a.tablespace_name “tb_name”,
total / (1024 * 1024) “tb_size(M)”,
free / (1024 * 1024) “tb_free_size(M)”,
(total - free) / (1024 * 1024 ) “tb_used_size(M)”,
total / (1024 * 1024 * 1024) “tb_sizeG)”,
free / (1024 * 1024 * 1024) “tb_free_size(G)”,
(total - free) / (1024 * 1024 * 1024) “tb_used_size(G)”,
round((total - free) / total, 4) * 100 “tb_usage %”
FROM (SELECT tablespace_name, SUM(bytes) free
FROM dba_free_space
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) total
FROM dba_data_files
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name;

7.释放表空间，通过move表空间的方式释放已经清理的存储空间。

这里需要注意的是move是move清理完后实际存在数据量。比如你表空间有1.5T，现在使用了1T，通过delete之后还剩100G，那么move主要就是针对这剩余的实际数据量100G操作的，就需要有200G的空间，通过delete操作表空间目前实际剩余1.4T，1.4T>200G，那么就完全OK，另外move的话剩余的实际数据越少越快，建议清理的话最好全部删除，那么move就是秒级别的。

ALTER TABLE loading***_tab对象表名MOVE
TABLESPACE sadata对象表空间
LOB ( loadingdata大字段字段名 ) STORE AS (
TABLESPACE sa***data表空间名和原表空间名一致
);

8.再次查询表空间使用情况，发现表空间已经释放，使用率下降到8.83%。

SELECT a.tablespace_name “tb_name”,
total / (1024 * 1024) “tb_size(M)”,
free / (1024 * 1024) “tb_free_size(M)”,
(total - free) / (1024 * 1024 ) “tb_used_size(M)”,
total / (1024 * 1024 * 1024) “tb_sizeG)”,
free / (1024 * 1024 * 1024) “tb_free_size(G)”,
(total - free) / (1024 * 1024 * 1024) “tb_used_size(G)”,
round((total - free) / total, 4) * 100 “tb_usage %”
FROM (SELECT tablespace_name, SUM(bytes) free
FROM dba_free_space
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) total
FROM dba_data_files
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name;

9.最后，重建一下索引就可以了。

ALTER INDEX loadingdata_pk REBUILD;
ALTER INDEX loadingdata_pk REBUILD ONLINE;

将上述清理SQL语句写成脚本，加入crontab，根据需要定期执行就可以了！O(∩_∩)O

注意：如果blob表没有清空，那么剩余表空间容量必须多余剩余数据容量。

*以上经验都是自己测试所得，如有错误请指正，谢谢！
数据无价，建议清理之前备份需要清理的表空间！实际操作应先在测试环境测试完成后，再在生产环境执行。