Oracle查看对象占用空间

如何像MySQL一样,查看表和索引的占用空间呢?

1. with t1 as
   
2. (
   
3.     select
   
4.     t.owner,
   
5.     t.table_name,
   
6.     i.index_name,
   
7.     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,
   
8.     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
   
9.     from dba_tables t
   
10.     left join dba_indexes i on t.table_name=i.table_name
    
11.     where t.owner like 'MVBOX%'
    
12. )
    
13. select
    
14. t1.owner as "所有者",
    
15. t1.table_name as "表名称",
    
16. t1.index_name as "索引名称",
    
17. t1.tablesize || 'G' as "表占用空间",
    
18. t1.indexsize || 'G' as "索引占用空间",
    
19. SUM(indexsize) over(PARTITION BY owner,table_name ) || 'G' as "索引占用总空间",
    
20. tablesize+SUM(indexsize) over(PARTITION BY owner,table_name ) || 'G' as "表和索引占用总空间"
    
21. from t1
    
22. order by tablesize desc,indexsize desc

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29254281/viewspace-1744846/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29254281/viewspace-1744846/