Oracle的LOB（CLOB）大字段以及（SYS_LOB***$$）清理
翰墨文海 人气:1文章结构如下:
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，并且独立于原始表存在。
1) 清理建的lob列的表
如果需要清理，可以truncate 该PAY_LOG_DETAILS表，或者drop不需要的分区（如果是分区表）
2) shrink的lob列的表
比如说在你大量的删除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;--关闭行移动
3) 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语句是，需要更大的开销，影响性能；比如，可以考虑做一个文件服务器，把需要的大对象按照一定的格式（如时间格式）存成文件，数据库中存放指定的地址就行，这样可以大大提高性能。
5.可以提供的资料
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
加载全部内容