Windows

删除Oracle的大字段占用存储空间的方法

Oracle数据库中图片或者包含二进制字段的那些大表，发现有类似SYS_LOB00000**2$$这些信息占用大量存储空间，在测试数据库或者不重要的临时表中，可以删除并释放存储空间

image002

Oracle 数据库中varchar2值为4000，针对 VARCHAR2 满足不了我们的需要时，Oracle就提出了大数据类型LOB( Large Object，大对象)。

Oarcle中的LOB类型：

在Oracle中，LOB(Large Object，大型对象)类型的字段现在用得越来越多了。因为这种类型的字段，容量大(最多能容纳4GB的数据)，且一个表中可以有多个这种类型的字段，很灵活，适用于数据量非常大的业务领域(如图像、档案等)。

LOB类型分为BLOB和CLOB两种：BLOB即二进制大型对象(Binary Large Object)，适用于存贮非文本的字节流数据(如程序、图像、影音等)。

而CLOB，即字符型大型对象(Character Large Object)，则与字符集相关，适于存贮文本型的数据(如历史档案、大部头著作等)。

查询LOB大字段的命令

通过以下命令查询出占用大量空间的字段和存储容量

1
2
3
4
5
6
7
8
9
10
11
SELECT SEGMENT_NAME, ROUND(SUM(BYTES / 1024 / 1024 / 1024), 2) G

  FROM DBA_SEGMENTS

 WHERE SEGMENT_NAME IN

       (SELECT TABLE_NAME FROM USER_TAB_COLUMNS WHERE DATA_TYPE = 'CLOB')

 GROUP BY SEGMENT_NAME

 ORDER BY 2 DESC;
123

查询类似SYS_LOB0**2$$命令

1
2
3
4
5
6
7
8
9
10
11
SELECT SEGMENT_NAME, ROUND(SUM(BYTES / 1024 / 1024 / 1024), 2) G

  FROM DBA_SEGMENTS

 --WHERE SEGMENT_NAME IN

    --   (SELECT TABLE_NAME FROM USER_TAB_COLUMNS WHERE DATA_TYPE = 'CLOB')

 GROUP BY SEGMENT_NAME

 ORDER BY 2 DESC;

详细命令命令

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SELECT B.TABLE_NAME,

      B.COLUMN_NAME,

      A.SEGMENT_NAME,

      a.SEGMENT_TYPE,

      ROUND(SUM(A.BYTES / 1024 / 1024 / 1024), 2) G

 FROM DBA_SEGMENTS A

 LEFT JOIN DBA_LOBS B

   ON A.OWNER = B.OWNER

  AND A.SEGMENT_NAME = B.SEGMENT_NAME

--WHERE B.SEGMENT_NAME = 'SYS_LOB0000026212C00002$$'

HAVING ROUND(SUM(A.BYTES / 1024 / 1024 / 1024), 2) >1

GROUP BY B.TABLE_NAME, B.COLUMN_NAME, A.SEGMENT_NAME,a.SEGMENT_TYPE;

查询后可以看到T_XQ_MJCRJL表的列KMRLTP,其中大对象单独存放在SYS_LOB0000107679C00016$$段中，LOBSEGMENT保存了lob列的真正数据，会非常大，并且独立于原始表存在。

清理建的lob列的表

如果需要清理，可以truncate T_XQ_MJCRJL表，或者drop不需要的分区。

shrink处理lob列的表

生产上如果使用表频繁的DML操作不建议shrink,会卡死，引起严重的业务后果

比如说在你大量的删除T_XQ_MJCRJL后（高水位没有下降），需要执行收缩，收缩的时候建议在不要在业务高峰期（否则可能引起很大的性能问题）。

Shrink对应的表语句如下：

注意：由于在线上，不能进行有表锁的操作，所以我并没有采用这种办法

alter table TABLE_NAME enable ROW MOVEMENT;–启动行移动功能

alter table TABLE_NAME shrink space compact; –只整理碎片 不回收空间

– 重置高水位，此时不能有DML操作

alter table TABLE_NAME shrink space; –整理碎片并回收空间，并调整水位线。业务少时执行

alter table TABLE_NAME disable ROW MOVEMENT;–关闭行移动

LOB建议

可能对一般的oracle dba或者其它人员，来说，并不是所有东西都要存入数据库，在设计表结构的时候（叫所谓的建模吧！），就应该考虑是否应该存在大字段，需要满足什么功能，好不好管理，对数据集的性能影响有多大？

个人不太建议在数据库中使用大对象,可以使用外部表（管理方便，更加的简单粗暴），大对象所对应的表进行DML语句是，需要更大的开销，影响性能；比如，可以考虑做一个文件服务器，把需要的大对象按照一定的格式（如时间格式）存成文件，数据库中存放指定的地址就行，这样可以大大提高性能。

原文参考：Oracle的LOB（CLOB）大字段以及（SYS_LOB***$$）清理 - 翰墨文海 - 博客园 (cnblogs.com)

欢迎关注公众号收藏小程序

河洛先生河洛先生