脚本分享 查询表大小

2024年 4月 30日 80.4k 0

搞了辣么多年DBA,总有点小东西可以分享下,技术又不好,就分享点脚本吧

查询包括表相关的索引、lob都给一起查出来了。没啥好说的直接上脚本(可以筛选用户或者表)

--查看表大小(包含索引和lob)
select /*统计视图*/
table_name,
table_total,
index_total,
lob_total,
table_total + index_total + lob_total total
from (select /*转换单位*/
q.name table_name,
round(sum(q.t / 1024 / 1024 / 1024), 3) table_total,
nvl(round(sum(w.t / 1024 / 1024 / 1024), 3), 0) index_total,
nvl(round(sum(e.t / 1024 / 1024 / 1024), 3), 0) lob_total
from ( /*所有表*/
select a.owner, a.segment_name name, sum(a.bytes) t
from dba_segments a
left join dba_tables b
on a.segment_name = b.table_name
and a.owner = b.owner
group by a.owner, a.segment_name) q
left join ( /*所有索引*/
select a.owner, c.table_name name, sum(a.bytes) t
from dba_segments a
left join dba_indexes c
on a.segment_name = c.index_name
and a.owner = c.owner
group by a.owner, c.table_name) w
on q.owner = w.owner
and q.name = w.name
left join ( /*所有LOB*/
select a.owner, d.table_name name, sum(a.bytes) t
from dba_segments a
left join dba_lobs d
on a.segment_name = d.segment_name
and a.owner = d.owner
group by a.owner, d.table_name) e
on q.owner = e.owner
and q.name = e.name --where q.owner = 'SYS' and q.name='AAA'
group by q.name, q.owner)
order by 5 desc;

不让搞魔力值计划,我的描述太少了,代码最多占80%

相关文章

Oracle如何使用授予和撤销权限的语法和示例
Awesome Project: 探索 MatrixOrigin 云原生分布式数据库
下载丨66页PDF,云和恩墨技术通讯(2024年7月刊)
社区版oceanbase安装
Oracle 导出CSV工具-sqluldr2
ETL数据集成丨快速将MySQL数据迁移至Doris数据库

发布评论