[Oracle]資料表的LOB欄位過大的處理方式

當資料表LOB欄位過大，可以將其移動到其他表空間。

執行語法如下
ALTER TABLE [schema].[table] MOVE LOB([lob_column]) STORE AS (TABLESPACE [tablespace]);

查看oracle的資料表大小(含blob欄位)

 select * from (

select a.owner, a.segment_name, a.MB+nvl(b.MB,0) mb from (

SELECT owner,segment_name,SUM(bytes)/1024/1024 MB

FROM dba_segments

WHERE segment_type='TABLE'

group by owner,segment_name) a

left join (

            SELECT s.owner,s.segment_name,s.segment_type,l.table_name,SUM(s.bytes)/1024/1024 MB

            FROM dba_segments s

            join DBA_LOBS L on s.owner = l.owner and s.segment_name = l.segment_name

            group by s.owner,s.segment_name,s.segment_type,l.table_name) b on a.owner = b.owner and a.segment_name = b.table_name

) x

--where x.owner = '[schema_name]'

order by x.MB DESC