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+10

SQL> 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+10

SQL> alter table username.table_name disable row movement;

Table altered.