带有lob字段的表的收缩问题

1 查看app_vip用户下面占用比较大的segment是:

select segment_name,owner,sum(bytes)/1024/1024 "Space(MB)"

from dba_segments where owner='XX'

 group by segment_name,owner order by  "Space(MB)" desc

 

SYS_LOB0000109151C00005$$ XX  294944M

 

2 根据segment的到具体的表:

select * from dba_lobs a where a.segment_name='SYS_LOB0000109151C00005$$' ;

APP_VIP RPT_REPORTANNEX ANNEXCONTENT SYS_LOB0000109151C00005$$ CENTERDBT SYS_IL0000109151C00005$$

 

3 删除表的一部分数据和一些关联的表数据 --------------------------------------------------周翔提供

alter table XX nologging ;

alter table XX nologging ;

alter table XX nologging ;

alter table XX nologging ;

select * from user_triggers where table_name=XX;--查询出所有表上的触发器，并且禁用

 

delete XX where originalcode in (select id from XX where pubdate <= date'2011-09-30');

delete XX where originalcode in (select id from XX where pubdate <= date'2011-09-30');

delete XX  where originalcode in (select id from XX where pubdate <= date'2011-09-30');

delete XX where pubdate <= date'2011-09-30';

---恢复触发器的状态，重写日志

 

4  释放表的空间

alter table XX enable row movement;

 

alter table XX shrink space cascade;

或者直接释放ANNEXCONTENT字段

ALTER TABLE XX MODIFY LOB (ANNEXCONTENT) (SHRINK SPACE)