Ajustando tablespaces com facilidade

Uma tarefa recorrente na manutenção de um banco de dados Oracle é o de colocar objetos nos tablespaces corretos. Para facilitar o trabalho, recorri ao metadados. A rotina abaixo procura todas as tabelas, todos os índices e todos os LOBs de um schema e verifica se estão nos tablespaces indicados.

create or replace procedure 
  ajustar_tablespaces(p_schema varchar2, 
                      p_table_ts varchar2, 
                      p_index_ts varchar2, 
                      p_lob_ts varchar2) is
begin

  --Indices
  for r in 
   (select 'alter index '||p_schema||'.'|| index_name 
         ||' rebuild tablespace '||p_index_ts cmd 
    from all_indexes
    where owner=upper(p_schema) 
      and tablespace_name<>upper(p_index_ts))
  loop
    dbms_output.put_line(r.cmd);
    execute immediate r.cmd;
  end loop;

  --Tabelas
  for r in 
   (select 'alter table '||p_schema||'.'||table_name
         ||' move tablespace '||p_table_ts cmd 
    from all_tables 
    where owner=upper(p_schema) 
      and tablespace_name<>upper(p_table_ts))
  loop
    dbms_output.put_line(r.cmd);
    execute immediate r.cmd;
  end loop;

  --LOBs
  for r in 
   (select 'alter table '||p_schema||'.'||table_name
         ||' move lob('||column_name||') store as (TABLESPACE '||p_lob_ts||')' cmd 
    from all_lobs 
    where owner=upper(p_schema) 
      and tablespace_name<>upper(p_lob_ts))
  loop
    dbms_output.put_line(r.cmd);
    execute immediate r.cmd;
  end loop;
  
  --Reconstroi os indices
  for r in 
   (select 'alter index '||p_schema||'.'||index_name
         ||' rebuild' cmd  
    from all_indexes 
    where owner=p_schema 
      and status='UNUSABLE') 
  loop
    dbms_output.put_line(r.cmd);
    execute immediate r.cmd;
  end loop;

end;

Então, para corrigir os tablespaces dos objetos de um schema, eu poderia fazer uma chamada como esta:

  exec ajustar_tablespaces('INFO', 'ts_data01', 'ts_index02', 'ts_lobs');

Os comandos são sendo impressos para que se possa acompanhar o andamento do trabalho, principalmente se os objetos forem grandes.

Abaixo, o mesmo código como um bloco a fim de facilitar execuções ad-hoc.

declare
  p_schema varchar2(100):='ABXY';
  p_table_ts varchar2(100):='DTBS1'; 
  p_index_ts varchar2(100):='ITBS1'; 
  p_lob_ts varchar2(100):='LTBS1';
begin

  --Indices
  for r in 
   (select 'alter index '||p_schema||'.'|| index_name 
         ||' rebuild tablespace '||p_index_ts cmd 
    from all_indexes
    where owner=upper(p_schema) 
      and tablespace_name<>upper(p_index_ts))
  loop
    dbms_output.put_line(r.cmd);
    execute immediate r.cmd;
  end loop;

  --Tabelas
  for r in 
   (select 'alter table '||p_schema||'.'||table_name
         ||' move tablespace '||p_table_ts cmd 
    from all_tables 
    where owner=upper(p_schema) 
      and tablespace_name<>upper(p_table_ts))
  loop
    dbms_output.put_line(r.cmd);
    execute immediate r.cmd;
  end loop;

  --LOBs
  for r in 
   (select 'alter table '||p_schema||'.'||table_name
         ||' move lob('||column_name||') store as (TABLESPACE '||p_lob_ts||')' cmd 
    from all_lobs 
    where owner=upper(p_schema) 
      and tablespace_name<>upper(p_lob_ts))
  loop
    dbms_output.put_line(r.cmd);
    execute immediate r.cmd;
  end loop;
  
  --Reconstroi os indices
  for r in 
   (select 'alter index '||p_schema||'.'||index_name
         ||' rebuild' cmd  
    from all_indexes 
    where owner=p_schema 
      and status='UNUSABLE') 
  loop
    dbms_output.put_line(r.cmd);
    execute immediate r.cmd;
  end loop;

end;