Oracle查看对象占用空间代码,PLSQL代码库,德仔网

代码语言

代码分类

【PLSQL】 Oracle查看对象占用空间

作者: / 发布于2016/12/30/ 1052

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

试试其它关键字

同语言下

可能有用的

贡献的其它代码Label