记录一次对LOB字段进行回收空间的案例
今天接到运维人员反馈最近表空间增长有点过快。按现有的数据增长，预计年底前就需要扩容存储。而从DBA的角度盲目进行硬件扩容是一种不负责任的的表现，我们要搞清究竟是什么导致数据增长的，解决问题根本所在。下面把整个过程分享给各位小伙伴，希望能帮助到您。
问题现象
从运维人员那得知表空间加了没有多久就用完了，客户方原本打算做预算将存储进行扩容。但本人觉得还是要对客户方负责，评估一下到底需不要增加硬件。先看一下表空间使用率，用户数据主要在USERS表空间，大概3700GB。
[root@rac01 ~]# su - oracle
[oracle@rac01 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Tue Jun 18 14:46:45 2024
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> set linesize 200
col tablespace_name for a20
select df.tablespace_name,
count(*) files,
round(sum(df.bytes) / 1048576) size_mb,
round(sum(free.bytes) / 1048576, 2) free_mb,
100 - round(100.0 * sum(free.bytes) / sum(df.bytes), 2) pct_used,
round(100.0 * sum(free.bytes) / sum(df.bytes), 2) pct_free
from dba_data_files df,
(select tablespace_name,
file_id,
sum(bytes) bytes,
max(bytes) maxbytes
from dba_free_space
group by tablespace_name, file_id) free
where df.tablespace_name = free.tablespace_name(+)
and df.file_id = free.file_id(+)
group by df.tablespace_name
order by pct_used desc
/SQL> SQL> 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
TABLESPACE_NAME FILES SIZE_MB FREE_MB PCT_USED PCT_FREE
-------------------- ---------- ---------- ---------- ---------- ----------
SYSTEM 2 11850 137.63 98.84 1.16
USERS 118 3854888 73229 98.1 1.9
SYSAUX 2 44374 1593.38 96.41 3.59
UNDOTBS1 1 30355 9554.31 68.52 31.48
UNDOTBS2 1 28319 25664.06 9.38 90.62
SQL>
