[Oracle] - Alterando Tablespaces de tabelas e índices

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

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