Obtendo o tamanho dos segmentos por schemas de banco de dados: (Tabelas, Índices, LOBs)

Olá,

No artigo de Maio/2011 eu compartilhei uma instrução SQL que retorna o tamanho de todos os segmentos (Tabelas, Índices, LOBs, etc.) pertencentes a um um schema (o schema atual), através das views de dicionário de dados USER_*. O objetivo dessa instrução SQL é mostrar não só o tamanho de uma tabela como também o tamanho dos objetos dependentes dela como índices e LOBs. Agora irei compartilhar uma consulta SQL que utiliza as views DBA_* de forma a retornar o tamanho dos segmentos de tabelas, índices e LOBs por schema de banco de dados, conforme a seguir.
 

SQL> SELECT owner,
  2         data_mb,
  3         indx_mb,
  4         lob_mb,
  5         total_mb
  6      FROM (SELECT data.owner,
  7                   NVL(data_mb,0) data_mb,
  8                   NVL(indx_mb,0) indx_mb,
  9                   NVL(lob_mb,0) lob_mb,
 10                   NVL(data_mb,0) + NVL(indx_mb,0) + NVL(lob_mb,0) total_mb
 11              FROM (  SELECT owner,
 12                             ROUND(SUM(data_mb),2) data_mb
 13                        FROM (SELECT owner, data_mb
 14                                FROM (SELECT a.owner,
 15                                             b.bytes/1024/1024 AS data_mb
 16                                        FROM dba_tables a, dba_segments b
 17                                       WHERE a.owner = b.owner and a.table_name = b.segment_name))
 18                    GROUP BY owner) data,
 19                   (  SELECT a.owner,
 20                             ROUND(SUM(b.bytes/1024/1024),2) AS indx_mb
 21                        FROM dba_indexes a, dba_segments b
 22                       WHERE a.owner = b.owner and a.index_name = b.segment_name
 23                    GROUP BY a.owner) indx,
 24                   (  SELECT a.owner,
 25                             ROUND(SUM(b.bytes/1024/1024),2) AS lob_mb
 26                        FROM dba_lobs a, dba_segments b
 27                       WHERE a.owner = b.owner and a.segment_name = b.segment_name
 28                    GROUP BY a.owner) lob
 29             WHERE
 30             data.owner = indx.owner(+)
 31             AND data.owner = lob.owner(+))
 32  WHERE owner in ('SCHEMA01',
 33                  'SCHEMA02',
 34                  'SCHEMA03',
 35                  'SCHEMA04',
 36                  'SCHEMA05',
 37                  'SCHEMA06',
 38                  'SCHEMA07')
 39  ORDER BY owner;

OWNER                       DATA_MB    INDX_MB     LOB_MB   TOTAL_MB
------------------------ ---------- ---------- ---------- ----------
SCHEMA01                   16069.38   58428.25  174381.44  248879.07
SCHEMA02                      11618   43081.31    9064.94   63764.25
SCHEMA03                   93944.63     529311  206722.38  829978.01
SCHEMA04                       78.5     107.63      98.56     284.69
SCHEMA05                    2814.25     9761.5    14573.5   27149.25
SCHEMA06                    3211.88   13447.94     555.75   17215.57
SCHEMA07                    9777.44   41685.13  258100.44  309563.01

7 rows selected.