查询大字段对象脚本
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.