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';

Comments

8 respostas para “Alterando Tablespaces de tabelas e índices no Oracle”

  1. Avatar de Paulo
    Paulo

    Obrigado pela dica! Me ajudou 🙂
    Thank´s man

  2. Avatar de Fred
    Fred

    Cara, salvou a madrugada.
    Muito obrigado.

  3. Avatar de Greik
    Greik

    Bom dia.

    A dica é muito boa, mas ainda fica uma dúvida cruel.
    Como eu faço com tabelas que tenham campos do tipo long?

  4. Avatar de Marinho
    Marinho

    Show de bola! Matou um problema que estava tendo aqui para campos LOB e MATERIALIZED VIEWS.
    Show de bola!

    []s

  5. Avatar de Alexandre
    Alexandre

    Ótimo Post, em muitos lugares não achei tão facilmente.

    abs

  6. Avatar de Anderson

    Muito bom cara valeu….

  7. Avatar de Thais
    Thais

    Muito válido!!

  8. Avatar de luciano260207

    Boa tarde !
    Como seria para alterar todos os data type de varchar2 tamanho 30 para 80? De um determinado schema .
    Desde já agradeço a atenção .

Deixe um comentário

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

plugins premium WordPress