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';
Obrigado pela dica! Me ajudou 🙂
Thank´s man
Cara, salvou a madrugada.
Muito obrigado.
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?
Show de bola! Matou um problema que estava tendo aqui para campos LOB e MATERIALIZED VIEWS.
Show de bola!
[]s
Ótimo Post, em muitos lugares não achei tão facilmente.
abs
Muito bom cara valeu….
Muito válido!!
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 .