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. <\/p>\n
SELECT a.owner, a.tablespace_name, a.segment_name, b.num_rows,<\/p>\n
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<\/p>\n
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 <\/p>\n
\u2013 Tamanho de Cada Tabela<\/p>\n
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’
\u2013 AND segment_name like \u2018CLIENTE%\u2019
GROUP BY owner, tablespace_name, segment_name<\/p>\n
\u2013 Tamanho das Tabelas Por Usu\u00e1rio<\/p>\n
SELECT owner, round(sum(bytes\/1024\/1024),2) as Tamanho_MB
FROM dba_segments
GROUP BY owner<\/p>\n
\u2013 Tamanho Total das Tabelas<\/p>\n
SELECT round(sum(bytes\/1024\/1024),2) as Tamanho_MB
FROM dba_segments<\/p>\n
\u2013 % de Uso das TableSpaces<\/p>\n
SELECT a.TABLESPACE_NAME \u201cTableSpace Name\u201d,<\/p>\n
round(a.BYTES\/1024\/1024) \u201cMB Allocated\u201d,<\/p>\n
round((a.BYTES-nvl(b.BYTES, 0)) \/ 1024 \/ 1024) \u201cMB Used\u201d,<\/p>\n
nvl(round(b.BYTES \/ 1024 \/ 1024), 0) \u201cMB Free\u201d,<\/p>\n
round(((a.BYTES-nvl(b.BYTES, 0))\/a.BYTES)*100,2) \u201cPct Used\u201d,<\/p>\n
round((1-((a.BYTES-nvl(b.BYTES,0))\/a.BYTES))*100,2) \u201cPct Free\u201d<\/p>\n
FROM (SELECT TABLESPACE_NAME,<\/p>\n
sum(BYTES) BYTES<\/p>\n
FROM dba_data_files<\/p>\n
GROUP BY TABLESPACE_NAME) a,<\/p>\n
(SELECT TABLESPACE_NAME,<\/p>\n
sum(BYTES) BYTES<\/p>\n
FROM sys.dba_free_space<\/p>\n
GROUP BY TABLESPACE_NAME) b<\/p>\n
WHERE a.TABLESPACE_NAME = b.TABLESPACE_NAME (+)<\/p>\n
ORDER BY ((a.BYTES-b.BYTES)\/a.BYTES);<\/p>\n","protected":false},"excerpt":{"rendered":"
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 … Continue lendo