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

Sobre minha saída da Timbira

Há 14 anos, durante o PGConf.Brasil 2009, lá na UNICAMP em Campinas/SP, 4 pessoas se reuniram e idealizaram a criação da primeira empresa dedicada exclusivamente

Split brain

Já tem algum tempo que eu pensava em fazer isso e chegou a hora. Este blog vai se dividir em 2 partes a partir de

plugins premium WordPress