如何像MySQL一樣,檢視錶和索引的佔用空間呢?

with t1 as
(
select
t.owner,
t.table_name,
i.index_name,
nvl((select round(sum(BYTES)/1024/1024/1024,2) G from dba_segments where SEGMENT_NAME=t.table_name AND OWNER=t.owner),0) tablesize,
nvl((select round(sum(BYTES)/1024/1024/1024,2) G from dba_segments where SEGMENT_NAME=i.index_name AND OWNER=i.owner ),0) indexsize
from dba_tables t
left join dba_indexes i on t.table_name=i.table_name
where t.owner like 'MVBOX%'
)
select
t1.owner as "所有者",
t1.table_name as "表名稱",
t1.index_name as "索引名稱",
t1.tablesize || 'G' as "表佔用空間",
t1.indexsize || 'G' as "索引佔用空間",
SUM(indexsize) over(PARTITION BY owner,table_name ) || 'G' as "索引佔用總空間",
tablesize+SUM(indexsize) over(PARTITION BY owner,table_name ) || 'G' as "表和索引佔用總空間"
from t1
order by tablesize desc,indexsize desc

Oracle檢視物件佔用空間

相關文章