Tabelas muito grandes sao candidatas a ficarem em tablespaces separadas, isso se sao fruto de full table scan, principalmente. Seus segmentos serao criados todos em uma unica tablespace. Melhora signficativa em performance.

SELECT a.owner, a.tablespace_name, a.segment_name, b.num_rows,

round(sum(a.bytes/1024/1024),2) as Tamanho_MB –, extents as Num_extents
FROM dba_segments a, dba_tables b
WHERE — a.owner = ‘LOGIX’ AND
a.segment_type = ‘TABLE’
AND a.segment_name = b.table_name
AND a.owner = b.owner
AND a.tablespace_name = b.tablespace_name
–AND segment_name like ‘%LOTE%’
GROUP BY a.owner, a.tablespace_name, a.segment_name, b.num_rows
ORDER BY round(sum(a.bytes/1024/1024),2) DESC

O script acima podemos gerar a saida para criar os comandos para alterar os objetos de tablespace e alterarmos a tablespace da qual se encontram. Melhor estarem em tablespaces separadas.
 
 
 
— Maiores tabelas do banco – tamanho e registros

– Tamanho de Cada Tabela

SELECT owner, tablespace_name, segment_name,
round(sum(bytes/1024/1024),2) as Tamanho_MB –, extents as Num_extents
FROM dba_segments
WHERE owner = ‘SYSTEM’
AND segment_type = ‘TABLE’
– AND segment_name like ‘CLIENTE%’
GROUP BY owner, tablespace_name, segment_name

– Tamanho das Tabelas Por Usuário

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

– Tamanho Total das Tabelas

SELECT round(sum(bytes/1024/1024),2) as Tamanho_MB
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);