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 O backup on-line pode ser feito um tablespace por vez, diminuindo a quantidade de logs gerados durante o backup;

o Você pode transportar tablespaces entre bases (teste e produção por exemplo) sem ter que exportar e importar todos os dados;

o Você pode utilizar diferentes parâmetros de storage, particionamento, etc;

o Fica mais fácil monitorar o crescimento da base com várias aplicações se cada aplicação possuir suas próprias tablespaces;

o Separar índices de tabelas ainda é uma boa política, especialmente porquê os índices podem ser reconstruídos e as tabelas não;

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:

o Migrar tabelas com o comando:

ALTER TABLE nome_da_tabela MOVE TABLESPACE nome_do_novo_tablespace;

o Migrar índices com o comando:

ALTER INDEX nome_do_indice REBUILD TABLESPACE nome_do_novo_tablespace;

o 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';
Technorati Marcas: ,,