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

 

posted @   钱若梨花落  阅读(1315)  评论(0编辑  收藏  举报
(评论功能已被禁用)
编辑推荐:
· 记一次.NET内存居高不下排查解决与启示
· 探究高空视频全景AR技术的实现原理
· 理解Rust引用及其生命周期标识(上)
· 浏览器原生「磁吸」效果!Anchor Positioning 锚点定位神器解析
· 没有源码,如何修改代码逻辑?
阅读排行:
· 全程不用写代码,我用AI程序员写了一个飞机大战
· MongoDB 8.0这个新功能碉堡了,比商业数据库还牛
· 记一次.NET内存居高不下排查解决与启示
· DeepSeek 开源周回顾「GitHub 热点速览」
· 白话解读 Dapr 1.15:你的「微服务管家」又秀新绝活了
点击右上角即可分享
微信分享提示