Oracle的LOB（CLOB）大字段以及（SYS_LOB***$$）清理

家家户户个人

已于 2023-11-24 15:12:46 修改

阅读量2.3k

点赞数

文章标签： oracle 数据库

于 2023-11-24 12:19:21 首次发布

本文链接：https://blog.csdn.net/qq_38696286/article/details/134595844

版权

1.背景：

生产上查询那些大表然后进行清理，然而发现有SYS_LOB0000093441C00002$$这中表段占用30G（只保留一个月，如果保留更久会更大）。

2.LOB介绍

Oracle 数据库中varchar2只能值为4000，PL/SQL中 VARCHAR2 变量类型，字节长度为32767，针对 VARCHAR2 满足不了我们的需要时，Oracle就提出了大数据类型LOB( Large Object，大对象)。

Oarcle中的LOB类型：

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

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

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

3.LOB大字段的清理(或者处理办法)

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;
在这里插入图片描述

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;
在这里插入图片描述

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;
在这里插入图片描述

经查看，PAY_LOG_DETAILS表这一列CONTENT建的clob,其中大对象单独存放在SYS_LOB0000093441C00002$$ 这个段中，LOBSEGMENT保存了lob列的真正数据，会非常大30G，并且独立于原始表存在。

清理建的lob列的表
如果需要清理，可以truncate 该PAY_LOG_DETAILS表，或者drop不需要的分区（如果是分区表）
shrink的lob列的表
生产上如果使用表频繁的DML操作不建议shrink,会卡死，引起严重的业务后果

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

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;–关闭行移动

ASK tom 大佬提到db_securefile，外部表
我自己也只是扫了一眼该回复，经过查看11g允许创建SecureFiles（默认值）PERMITTED模式，他的思想是迁移到SecureFiles文件或者是我们熟知的外部表那种（直接存在外部表里管理更简单）

提供网页如下：

https://asktom.oracle.com/pls/apex/asktom.search?tag=reclaimreuse-lob-space

4. LOB建议

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

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

５．可以提供的资料

https://asktom.oracle.com/pls/apex/asktom.search?tag=reclaimreuse-lob-space

https://connor-mcdonald.com/2015/07/03/continuous-delivery-moving-to-securefile/

http://www.oracle.com/technetwork/articles/sql/11g-securefiles-084075.html

６．感谢原创

https://www.cnblogs.com/hmwh/p/12380211.html