Alterando Tablespaces de tabelas e índices no Oracle

A tarefa é trivial, mas não é algo que você faz todo dia. Então resolvi documentar aqui para facilitar a minha vida. Há várias formas diferentes de se rearranjar tablespaces. Com a popularização dos RAIDs, não é mais tão comum ficar dividindo tablespaces através de discos isolados, mas ainda assim, há bons motivos para você criar todos os objetos em apenas um tablespace:

  • O backup on-line pode ser feito um tablespace por vez, diminuindo a quantidade de logs gerados durante o backup;
  • Você pode transportar tablespaces entre bases (teste e produção por exemplo) sem ter que exportar e importar todos os dados;
  • Você pode utilizar diferentes parâmetros de storage, particionamento, etc;
  • Fica mais fácil monitorar o crescimento da base com várias aplicações se cada aplicação possuir suas próprias tablespaces;
  • Separar índices de tabelas ainda é uma boa política, especialmente porquê os índices podem ser reconstruídos e as tabelas não;
  • Objetos especiais como LOBs e dados estáticos são bons candidatos a terem seu próprio tablespace;

Assim sendo, é comum você pegar uma tabela que cresceu muito e alocar um tablespace só para ela e coisas do tipo. Particularmente, quando os desenvolvedores tem a liberdade de criar objetos no ambiente de testes (sim, isso é polêmico e fonte para outra conversa), é comum ter que ajustar os parâmetros de storage antes de colocar os objetos no ambiente de homologação ou produção. Seja qual for o motivo da movimentação, você terá que fazer a migração em 3 etapas:

  • Migrar tabelas com o comando:
ALTER TABLE nome_da_tabela MOVE TABLESPACE nome_do_novo_tablespace
;
  • Migrar índices com o comando:
ALTER INDEX nome_do_indice REBUILD TABLESPACE nome_do_novo_tablespace
;
  • Migrar LOBs com o comando:
ALTER TABLE nome_da_tabela MOVE LOB(nome_da_coluna_lob)
    STORE AS (nome_do_novo_tablespace);

Note tabelas que contem LOBs, possuem um índice que aparece na tabela DBA_INDEXES com data_type do tipo LOB. Se você tentar reconstruir estes índices em outro tablespace você terá um erro do tipo: “ORA-02327: cannot create index on expression with datatype LOB”. Por isso é importante a etapa de migração dos LOBs.

Segue aqui um script para fazer isso rapidamente num para todos objetos de um determinado esquema:

SELECT 'ALTER TABLE nome_do_esquema.' || table_name || ' MOVE TABLESPACE nome_do_novo_tablespace;'
  FROM dba_tables
  WHERE
    owner = 'nome_do_esquema';

SELECT 'ALTER INDEX nome_do_esquema.' || index_name || ' REBUILD TABLESPACE nome_do_novo_tablespace;'
  FROM dba_indexes
  WHERE
    owner = 'nome_do_esquema' AND
    index_type != 'LOB';

SELECT
  'ALTER TABLE nome_do_esquema.' || table_name ||
  ' MOVE LOB( ' || COLUMN_NAME ||
  ' ) STORE AS (TABLESPACE nome_do_novo_tablespace);'
  FROM dba_tab_columns
  WHERE
    owner = 'nome_do_esquema' AND
    data_type LIKE '%LOB';

Compartilhe

Você pode gostar

pg_hba.conf

Introdução O arquivo pg_hba.conf (PostgreSQL Host-Based Authentication) é uma peça fundamental na configuração de segurança de qualquer instância PostgreSQL. Ele define as regras de autenticação

Tuning de SO (no Linux)

Introdução Tuning refere-se ao processo de ajustar e otimizar o desempenho de um sistema, software ou aplicação. A otimização do sistema operacional é uma etapa

Tipos de cargas dos bancos de dados

Introdução Cargas de dados referem-se aos diferentes tipos de operações e transações que um banco de dados deve processar. Essas cargas variam conforme o tipo

plugins premium WordPress