清理 oracle lobsegment

来源：互联网发布：民间子平何知章388句编辑：程序博客网时间：2024/06/04 18:25

查询到表空间有个对象占用空间非常大，达200G

SQL> select * from (  2  select owner,segment_name,tablespace_name,segment_type,bytes/1024/1024 from dba_segments order by 5 desc) where TABLESPACE_NAME='USERS' AND rownum<20;  3  OWNER                          SEGMENT_NAME------------------------------ -----------------------------------TABLESPACE_NAME                SEGMENT_TYPE       BYTES/1024/1024------------------------------ ------------------ ---------------UNIGUARD                       SYS_LOB0000052424C00006$$USERS                          LOBSEGMENT                  204859UNIGUARD                       IMAGE_FILEUSERS                          TABLE                        10826UNIGUARD                       CAR_TRACK_10USERS                          TABLE                         4097

LOBSEGMENT的系统命名规则是

SYS_LOB(10 digit object_id)C(5 digit col#)$$ 表段
SYS_IL(10 digit object_id)C(5 digit col#)$$ 索引

SQL> select object_name,status from dba_objects where object_id='0000052424';OBJECT_NAME--------------------------------------------------------------------------------STATUS-------IMAGE_FILEVALID

原来是image_file使用了lob字段，为了释放出空间，我把此表导出，重建，再导入。结果空间释放。