Tamanho de uma tabela

Ola pessoal,

venho a mostrar a voces como temos o tamanho de uma tabela e de seus indices

– Tamanho de Cada Tabela

SELECT substr(owner,1,15) owner,segment_type, 
substr(tablespace_name,1,15) tablespace_name, 
round(sum(bytes/1024/1024),2) as Tamanho_MB 
FROM dba_segments
WHERE owner = ‘SANKHYA’
AND segment_type = ‘TABLE’
AND segment_name like ‘TSILGT%’
GROUP BY owner,segment_type, tablespace_name, segment_name
union all
SELECT substr(owner,1,15) owner,segment_type, 
substr(tablespace_name,1,15) tablespace_name, 
round(sum(bytes/1024/1024),2) as Tamanho_MB 
FROM dba_segments
WHERE owner = ‘SANKHYA’
AND segment_type = ‘INDEX’
AND segment_name in (
select index_name 
from dba_indexes 
where table_name = ‘TSILGT’
and owner = ‘SANKHYA’)
GROUP BY owner,segment_type, tablespace_name, segment_name;
SELECT owner, tablespace_name, segment_name,
round(sum(bytes/1024/1024),2) as Tamanho_MB –, extents as Num_extents
FROM dba_segments
WHERE owner = ‘SCOTT’
AND segment_type = ‘TABLE’
— AND segment_name like ‘DEPT%’
GROUP BY owner, tablespace_name, segment_name

– Tamanho das Tabelas Por Usuário

SELECT owner, round(sum(bytes/1024/1024),2) as Tamanho_MB –, extents as Num_extents
FROM dba_segments
GROUP BY owner

– Tamanho Total das Tabelas

SELECT round(sum(bytes/1024/1024),2) as Tamanho_MB –, extents as Num_extents
FROM dba_segments

– % de Uso das TableSpaces

SELECT a.TABLESPACE_NAME “TableSpace Name”,
round(a.BYTES/1024/1024) “MB Allocated”,
round((a.BYTES-nvl(b.BYTES, 0)) / 1024 / 1024) “MB Used”,
nvl(round(b.BYTES / 1024 / 1024), 0) “MB Free”,
round(((a.BYTES-nvl(b.BYTES, 0))/a.BYTES)*100,2) “Pct Used”,
round((1-((a.BYTES-nvl(b.BYTES,0))/a.BYTES))*100,2) “Pct Free”
FROM (SELECT TABLESPACE_NAME,
sum(BYTES) BYTES
FROM dba_data_files
GROUP BY TABLESPACE_NAME) a,
(SELECT TABLESPACE_NAME,
sum(BYTES) BYTES
FROM sys.dba_free_space
GROUP BY TABLESPACE_NAME) b
WHERE a.TABLESPACE_NAME = b.TABLESPACE_NAME (+)
ORDER BY ((a.BYTES-b.BYTES)/a.BYTES);

Deixe um comentário

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *