oracle 查询 LOB 字段大小

select distinct ab.TABLE_NAME,
                ab.SEGMENT_NAME,
                ab.TABLESPACE_NAME,
                ab.OWNER,
                sum(ac.BYTES / 1024 / 1024 / 1024) GB
  from dba_lobs ab, DBA_SEGMENTS ac
 where ab.OWNER IN (SELECT USERNAME
                      FROM DBA_USERS A
                     WHERE A.account_status = 'OPEN'
                       AND A.default_tablespace NOT IN
                           ('SYSTEM',
                            'USERS',
                            'SYSAUX',))
   and ab.SEGMENT_NAME = ac.segment_name
   and ab.OWNER = ac.owner
--and ab.TABLESPACE_NAME = 'LOB_DATA'
 group by ab.TABLE_NAME, ab.SEGMENT_NAME, ab.TABLESPACE_NAME, ab.OWNER
 order by 5 desc

---针对单表(分区表)查询 单个分区LOB 字段大小

SELECT DLP.TABLE_NAME,
       DLP.COLUMN_NAME,
       DLP. PARTITION_NAME,
       DS.BYTES / 1024 / 1024 / 1024 GB
  FROM DBA_LOB_PARTITIONS DLP
  LEFT JOIN DBA_SEGMENTS DS
    ON (DLP.LOB_PARTITION_NAME = DS.PARTITION_NAME)
 WHERE DLP.TABLE_NAME = 'T_TEST
   AND DS.SEGMENT_NAME = 'SYS_LOB0000318949C00003$$';

 

---汇总----查询LOB字段每个分区的使用量
select w.partition_name, w.gg, r.partition_name, r.table_name
from (SELECT round(sum(bytes / 1024 / 1024 / 1024), 2) gg,
e.segment_name,
q.partition_name,
e.TABLE_NAME
FROM DBA_SEGMENTS Q, dba_lobs e
WHERE Q.segment_name = e.SEGMENT_NAME
and e.TABLESPACE_NAME = 'LOB_DATA'
group by e.segment_name, q.partition_name, e.TABLE_NAME
order by 1 desc) w,
dba_lob_partitions r
where w.gg > 1
and r.lob_partition_name = w.partition_name
order by r.partition_name