Tutorial: Gerenciamento de Tablespaces no Oracle Database

O que é um Tablespace?

Um tablespace é a unidade lógica de armazenamento no Oracle Database. Ele agrupa logicamente segmentos de dados (tabelas, índices, etc.) e é composto fisicamente por um ou mais datafiles no sistema operacional.

Todo banco Oracle possui ao menos os seguintes tablespaces padrão:

Tablespace Descrição
SYSTEM Dicionário de dados e objetos internos do Oracle
SYSAUX Componentes auxiliares (AWR, statspack, etc.)
TEMP Operações temporárias (sorts, joins, group by)
UNDOTBS1 Armazenamento de dados de undo (rollback)
USERS Tablespace padrão para objetos de usuários

Listando Tablespaces

Listar todos os tablespaces do banco

SELECT
    tablespace_name,
    status,
    contents,
    logging,
    extent_management,
    segment_space_management
FROM dba_tablespaces
ORDER BY tablespace_name;

Listar os datafiles associados a cada tablespace

SELECT
    tablespace_name,
    file_name,
    bytes / 1024 / 1024        AS tamanho_mb,
    autoextensible,
    maxbytes / 1024 / 1024     AS tamanho_max_mb
FROM dba_data_files
ORDER BY tablespace_name, file_name;

Listar tempfiles (tablespaces temporários)

SELECT
    tablespace_name,
    file_name,
    bytes / 1024 / 1024    AS tamanho_mb,
    autoextensible
FROM dba_temp_files
ORDER BY tablespace_name;

Verificando Uso e Espaço Livre

Ver espaço livre por tablespace

SELECT
    df.tablespace_name                                      AS tablespace,
    ROUND(df.total_mb, 2)                                   AS total_mb,
    ROUND(fs.free_mb, 2)                                    AS livre_mb,
    ROUND(df.total_mb - fs.free_mb, 2)                      AS usado_mb,
    ROUND((df.total_mb - fs.free_mb) / df.total_mb * 100, 2) AS pct_usado
FROM
    (SELECT tablespace_name, SUM(bytes) / 1024 / 1024 AS total_mb
     FROM dba_data_files
     GROUP BY tablespace_name) df
JOIN
    (SELECT tablespace_name, SUM(bytes) / 1024 / 1024 AS free_mb
     FROM dba_free_space
     GROUP BY tablespace_name) fs
ON df.tablespace_name = fs.tablespace_name
ORDER BY pct_usado DESC;

Atenção: Tablespaces com mais de 85% de uso merecem atenção imediata para evitar erros como ORA-01653: unable to extend table.

Ver detalhes completos incluindo autoextend

SELECT
    d.tablespace_name,
    d.file_name,
    ROUND(d.bytes / 1024 / 1024, 2)        AS tamanho_atual_mb,
    d.autoextensible,
    ROUND(d.maxbytes / 1024 / 1024, 2)     AS tamanho_max_mb,
    ROUND(f.bytes / 1024 / 1024, 2)        AS espaco_livre_mb
FROM dba_data_files d
LEFT JOIN dba_free_space f ON d.file_id = f.file_id
ORDER BY d.tablespace_name;

Aumentando Tablespaces

Existem duas formas principais de aumentar um tablespace:

  1. Redimensionar um datafile existente (resize)
  2. Adicionar um novo datafile ao tablespace

Opção 1: Redimensionar um datafile existente

Use este comando quando quiser aumentar o tamanho de um arquivo já existente:

-- Sintaxe
ALTER DATABASE DATAFILE '<caminho_completo_do_arquivo>' RESIZE <novo_tamanho>;

-- Exemplo: aumentar para 2 GB
ALTER DATABASE DATAFILE '/u01/oradata/ORCL/users01.dbf' RESIZE 2048M;

Verifique o caminho correto do datafile com a query dba_data_files antes de executar.

Opção 2: Aumentar tablespace temporário

Para tablespaces do tipo TEMP, use TEMPFILE no lugar de DATAFILE:

ALTER DATABASE TEMPFILE '/u01/oradata/ORCL/temp01.dbf' RESIZE 1024M;

Habilitando Autoextend

O autoextend permite que o Oracle expanda automaticamente o datafile quando o espaço acabar, até um limite definido.

Ativar autoextend em um datafile existente

-- Sintaxe
ALTER DATABASE DATAFILE '<caminho_do_arquivo>'
    AUTOEXTEND ON
    NEXT <incremento>
    MAXSIZE <tamanho_maximo>;

-- Exemplo: incremento de 512 MB até o máximo de 10 GB
ALTER DATABASE DATAFILE '/u01/oradata/ORCL/users01.dbf'
    AUTOEXTEND ON
    NEXT 512M
    MAXSIZE 10240M;

Desativar autoextend

ALTER DATABASE DATAFILE '/u01/oradata/ORCL/users01.dbf' AUTOEXTEND OFF;

Parâmetros do Autoextend

Parâmetro Descrição
NEXT Tamanho do incremento de cada extensão automática
MAXSIZE Tamanho máximo que o arquivo pode atingir
UNLIMITED Permite crescimento até o limite do sistema de arquivos (cuidado!)

Adicionando Novos Datafiles

Quando não é possível (ou desejável) aumentar os arquivos existentes, a melhor alternativa é adicionar um novo datafile ao tablespace.

Adicionar datafile a um tablespace existente

-- Sintaxe
ALTER TABLESPACE <nome_tablespace>
    ADD DATAFILE '<caminho_novo_arquivo>'
    SIZE <tamanho>
    AUTOEXTEND ON
    NEXT <incremento>
    MAXSIZE <maximo>;

-- Exemplo: adicionar novo datafile de 1 GB ao tablespace USERS
ALTER TABLESPACE USERS
    ADD DATAFILE '/u01/oradata/ORCL/users02.dbf'
    SIZE 1024M
    AUTOEXTEND ON
    NEXT 256M
    MAXSIZE 4096M;

Adicionar tempfile a tablespace temporário

ALTER TABLESPACE TEMP
    ADD TEMPFILE '/u01/oradata/ORCL/temp02.dbf'
    SIZE 512M
    AUTOEXTEND ON
    NEXT 128M
    MAXSIZE 2048M;

Boas Práticas

Monitoramento

  • Crie alertas para tablespaces que ultrapassem 80% de uso.
  • Use a view dba_tablespace_usage_metrics para uma visão consolidada em bancos 11g+:
SELECT
    tablespace_name,
    ROUND(used_space * 8192 / 1024 / 1024, 2)     AS usado_mb,
    ROUND(tablespace_size * 8192 / 1024 / 1024, 2) AS total_mb,
    ROUND(used_percent, 2)                          AS pct_usado
FROM dba_tablespace_usage_metrics
ORDER BY used_percent DESC;

Organização dos Datafiles

  • Separe datafiles em diferentes discos para melhorar I/O.
  • Evite usar MAXSIZE UNLIMITED; defina sempre um limite seguro.
  • Prefira datafiles menores e múltiplos em vez de um único arquivo enorme.
  • Nomeie os arquivos de forma consistente: <tablespace>NN.dbf (ex: users01.dbf, users02.dbf).

Erros Comuns e Soluções

Erro Oracle Causa provável Solução
ORA-01653 Tablespace sem espaço livre Aumentar datafile ou adicionar novo
ORA-01652 Tablespace TEMP sem espaço Aumentar tempfile
ORA-19502 Erro ao escrever no datafile Verificar espaço em disco no SO
ORA-01144 Tamanho do arquivo excede o limite Usar múltiplos datafiles menores

Permissões necessárias: Para executar os comandos deste tutorial, o usuário precisa ter o privilégio DBA ou, no mínimo, ALTER TABLESPACE e acesso às views DBA_DATA_FILES, DBA_FREE_SPACE e DBA_TABLESPACES.