Oracle的LOB(CLOB)大字段以及(SYS_LOB***$$)清理

2019/04/10 10:10
阅读数 179

文章结构如下:

 

 

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

 

原文出处:https://www.cnblogs.com/hmwh/p/12380211.html

展开阅读全文
打赏
0
0 收藏
分享
加载中
更多评论
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部