Gerenciamento de Tablespaces e Datafiles

Tablespaces Padrão do Oracle

Tablespace Undo

-- Criar tablespace undo
CREATE UNDO TABLESPACE undotbs2
  DATAFILE '/u02/oradata/ORCL/undotbs02.dbf' SIZE 500M
  AUTOEXTEND ON;

-- Trocar para novo undo tablespace (dinâmico)
ALTER SYSTEM SET undo_tablespace = undotbs2;

-- Verificar undo tablespace ativo
SHOW PARAMETER undo_tablespace;

-- Após migração bem-sucedida, pode-se dropar o antigo
DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;

Monitorando Espaço

-- Ver uso de espaço por tablespace
SELECT
    t.tablespace_name,
    ROUND(SUM(d.bytes)/1024/1024, 2) AS total_mb,
    ROUND(SUM(CASE WHEN f.bytes IS NOT NULL THEN f.bytes ELSE 0 END)/1024/1024, 2) AS free_mb,
    ROUND((SUM(d.bytes) - SUM(CASE WHEN f.bytes IS NOT NULL THEN f.bytes ELSE 0 END))/1024/1024, 2) AS used_mb,
    ROUND(((SUM(d.bytes) - SUM(CASE WHEN f.bytes IS NOT NULL THEN f.bytes ELSE 0 END)) / SUM(d.bytes)) * 100, 2) AS pct_used
FROM dba_tablespaces t
LEFT JOIN dba_data_files d ON t.tablespace_name = d.tablespace_name
LEFT JOIN dba_free_space f ON d.file_id = f.file_id
WHERE t.contents = 'PERMANENT'
GROUP BY t.tablespace_name
ORDER BY pct_used DESC;

-- Ver datafiles e seu uso
SELECT
    file_name,
    tablespace_name,
    ROUND(bytes/1024/1024, 2) AS size_mb,
    ROUND(maxbytes/1024/1024, 2) AS maxsize_mb,
    autoextensible,
    status
FROM dba_data_files
ORDER BY tablespace_name, file_id;

-- Alertar sobre tablespaces com mais de 85% de uso
SELECT
    tablespace_name,
    ROUND((used_space/total_space) * 100, 2) AS pct_used
FROM (
    SELECT
        d.tablespace_name,
        SUM(d.bytes) AS total_space,
        SUM(d.bytes) - SUM(NVL(f.bytes, 0)) AS used_space
    FROM dba_data_files d
    LEFT JOIN dba_free_space f ON d.file_id = f.file_id
    GROUP BY d.tablespace_name
)
WHERE (used_space/total_space) > 0.85
ORDER BY pct_used DESC;

Movendo Tablespaces SYSTEM/SYSAUX

Tablespaces de sistema requerem que o banco esteja em modo MOUNT.

-- 1. Shutdown do banco
SHUTDOWN IMMEDIATE;

-- 2. Startup em modo MOUNT
STARTUP MOUNT;

-- 3. Copiar arquivo no SO
-- $ cp /u02/oradata/ORCL/system01.dbf /u03/oradata/ORCL/system01.dbf

-- 4. Renomear no control file
ALTER DATABASE RENAME FILE '/u02/oradata/ORCL/system01.dbf'
  TO '/u03/oradata/ORCL/system01.dbf';

-- 5. Abrir o banco
ALTER DATABASE OPEN;

-- 6. Remover arquivo antigo (após verificar que tudo está OK)
-- $ rm /u02/oradata/ORCL/system01.dbf

Usando OMF

-- Criar tablespace SEM especificar datafile (usa OMF)
CREATE TABLESPACE dados_omf_test;
-- Oracle cria automaticamente algo como: /u02/oradata/ORCL/datafile/o1_mf_dados_om_k2h8xp6t_.dbf

-- Adicionar datafile sem especificar nome
ALTER TABLESPACE dados_omf_test ADD DATAFILE;
-- Oracle cria automaticamente o nome

-- Dropar tablespace (arquivos são excluídos automaticamente!)
DROP TABLESPACE dados_omf_test INCLUDING CONTENTS AND DATAFILES;
-- Com OMF, os datafiles são apagados do SO automaticamente

-- Ver arquivos criados via OMF
SELECT file_name, tablespace_name, bytes/1024/1024 AS size_mb
FROM dba_data_files
WHERE file_name LIKE '%o1_mf%'
ORDER BY tablespace_name;

Alterando Tablespace para Bigfile

-- Processo para "converter" (na verdade, migrar):

-- 1. Criar novo tablespace BIGFILE
CREATE BIGFILE TABLESPACE dados_app_big
  DATAFILE '/u02/oradata/ORCL/dados_app_big01.dbf' SIZE 5G
  AUTOEXTEND ON;

-- 2. Mover tabelas para novo tablespace
ALTER TABLE minha_tabela MOVE TABLESPACE dados_app_big;

-- 3. Reconstruir índices (ficam inválidos após MOVE)
ALTER INDEX idx_minha_tabela REBUILD TABLESPACE dados_app_big;

-- Ou script para mover todos os objetos:
BEGIN
  FOR rec IN (SELECT table_name FROM dba_tables
              WHERE tablespace_name = 'DADOS_APP'
              AND owner = 'MYUSER') LOOP
    EXECUTE IMMEDIATE 'ALTER TABLE myuser.' || rec.table_name ||
                      ' MOVE TABLESPACE dados_app_big';
  END LOOP;
END;
/

-- 4. Após migração completa, dropar tablespace antigo
DROP TABLESPACE dados_app INCLUDING CONTENTS AND DATAFILES;

Gerenciamento de Tablespaces

-- Colocar tablespace OFFLINE
ALTER TABLESPACE dados_app OFFLINE NORMAL;
-- OFFLINE NORMAL: checkpoint + close files (mais seguro)
-- OFFLINE TEMPORARY: close files sem checkpoint (rápido, requer recovery)
-- OFFLINE IMMEDIATE: força offline imediatamente (requer recovery)

-- Colocar tablespace ONLINE
ALTER TABLESPACE dados_app ONLINE;

-- Fazer tablespace READ ONLY (útil para dados históricos)
ALTER TABLESPACE dados_historico READ ONLY;

-- Voltar para READ WRITE
ALTER TABLESPACE dados_historico READ WRITE;

-- Dropar tablespace (sem remover arquivos)
DROP TABLESPACE dados_app INCLUDING CONTENTS;
-- Arquivos ficam no disco - você precisa apagá-los manualmente

-- Dropar tablespace (removendo arquivos do SO)
DROP TABLESPACE dados_app INCLUDING CONTENTS AND DATAFILES;
-- Remove arquivos automaticamente do SO

-- Dropar tablespace com constraints referenciando objetos nele
DROP TABLESPACE dados_app INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

-- Alterar default tablespace de usuário
ALTER USER myuser DEFAULT TABLESPACE dados_app QUOTA UNLIMITED ON dados_app;

-- Definir quota (limite de espaço) para usuário
ALTER USER myuser QUOTA 500M ON dados_app;

-- Ver quotas de usuários
SELECT username, tablespace_name, bytes/1024/1024 AS used_mb, max_bytes/1024/1024 AS quota_mb
FROM dba_ts_quotas
ORDER BY username, tablespace_name;

-- Listar todos os tablespaces
SELECT tablespace_name, status, contents, extent_management, segment_space_management
FROM dba_tablespaces
ORDER BY tablespace_name;

-- Ver objetos em um tablespace
SELECT owner, segment_name, segment_type, bytes/1024/1024 AS size_mb
FROM dba_segments
WHERE tablespace_name = 'DADOS_APP'
ORDER BY bytes DESC;

O que você deve dominar após este tópico: