脚本分享 查询表大小
搞了辣么多年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;
