espaço total gasto pelos segmentos

[Alexandre Garcia 0]

Bom dia,

 

Algum camarada do grupo por acaso tem uma consulta que mostre o espaço total gasto pelos segmentos de um esquema e o percentual que cada segmento gasta.

 

Obrigado.

 

Abs.

[Alexandre Garcia 0]

Pessoal fiz da forma abaixo mas a saida nao está correta ainda o percentual está errado.

 

SELECT owner, 
 data_mb, 
 indx_mb, 
 lob_mb, 
 total_mb,
 ratio_to_report(total_mb) over () AS RR
 FROM (SELECT data.owner, 
 NVL(data_mb,0) data_mb, 
 NVL(indx_mb,0) indx_mb, 
 NVL(lob_mb,0) lob_mb, 
 NVL(data_mb,0) + NVL(indx_mb,0) + NVL(lob_mb,0) total_mb 
 FROM ( SELECT owner, 
 ROUND(SUM(data_mb),2) data_mb 
 FROM (SELECT owner, data_mb 
 FROM (SELECT a.owner, 
 b.bytes/1024/1024 AS data_mb 
 FROM dba_tables a, dba_segments b 
 WHERE a.owner = b.owner and a.table_name = b.segment_name)) 
 GROUP BY owner) data, 
 ( SELECT a.owner, 
 ROUND(SUM(b.bytes/1024/1024),2) AS indx_mb 
 FROM dba_indexes a, dba_segments b 
 WHERE a.owner = b.owner and a.index_name = b.segment_name 
 GROUP BY a.owner) indx, 
 ( SELECT a.owner, 
 ROUND(SUM(b.bytes/1024/1024),2) AS lob_mb 
 FROM dba_lobs a, dba_segments b 
 WHERE a.owner = b.owner and a.segment_name = b.segment_name 
 GROUP BY a.owner) lob 
 WHERE 
 data.owner = indx.owner(+) 
 AND data.owner = lob.owner(+)) 
 WHERE owner in ('LOCADORA','MEUESQUEMA') 
 ORDER BY owner;