LOBSEGMENT过大的处理

查询大字段对象脚本

SELECT A.TABLE_NAME,        A.COLUMN_NAME,        B.SEGMENT_NAME,        B.SEGMENT_TYPE,        B.TABLESPACE_NAME,        B.BYTES / 1024 / 1024,        B.BLOCKS,        B.EXTENTS   FROM USER_LOBS A, USER_SEGMENTS B WHERE A.SEGMENT_NAME = B.SEGMENT_NAME ORDER BY B.BYTES DESC;

查询到一个消息队列的表的CLOB字段引起的logsegment过大，57G大小，与开发人员联系删除一定的数据，当然之前要做好备份。

SQL> delete from schema.table_name nologging  where status=1 and starttime < sysdate-180 and createdate< sysdate-180;4993959 rows deleted.SQL> commit;Commit complete.SQL> select bytes from dba_segments where segment_name ='logsegment' and owner ='username';     BYTES----------6.2197E+10SQL> alter table username.table_name enable row movement;Table altered.SQL> alter table username.table_name  shrink space cascade;Table altered. SQL> select bytes from dba_segments where segment_name ='logsegment' and owner ='username';  BYTES----------2.6659E+10SQL> alter table username.table_name disable row movement;Table altered.