Procedure para gerar Índices por Schema no Oracle

  Dia destes precisei gerar um script de todos os índices de determinado esquema para executá-los em outro banco de dados em cliente, com o intuito de recriar os índices ausentes na base. Para isso então criei uma procedure que faz um output do script dos índices. Fiz ela conforme abaixo...

create or replace
PROCEDURE GERA_INDICES_CIGAM (p_owner varchar2 default 'CIGAM')
AS

/* Procedimento criado para gerar script de criação de indices de um esquema
   para aplicar em outro.
   
   Criado por: Alexsandro Haag 
   Criado em: 2010-09-23 (AAAA-MM-DD)
   Alterado em: 0000-00-00 (AAAA-MM-DD)
*/


/*Crio cursor com todos os indices do esquema escolhido*/
cursor c_indices (cv_owner varchar2)
 is select index_name,
           table_name,
           case  uniqueness
            when 'UNIQUE'
              then uniqueness
            else null
           end uniqueness,
           index_type
    from dba_indexes
    where owner = cv_owner
    order by table_name,
             index_name;

/*Crio cursor com as colunas dos indice do cursor acima*/
cursor c_campos_indices ( cv_owner varchar2,
                          cv_indice varchar2 )
  is select index_name,
            column_name,
            column_position,
            case descend
              when 'DESC' then descend
              else null
            end descend
     from dba_ind_columns
     where index_owner = cv_owner
     and index_name = cv_indice
     order by column_position;

     
v_comando varchar2(1000);
v_expressao varchar2(1000);

BEGIN

for rec_indices
  in c_indices (p_owner)
loop
 
    v_comando := 'create ' || rec_indices.uniqueness
                 ||' index ' || rec_indices.index_name
                 ||' on ' || rec_indices.table_name
                 ||'(';
    
    for rec_campos
      in c_campos_indices (p_owner,rec_indices.index_name)
    loop
   
       if rec_campos.column_position > 1
        then
          v_comando := v_comando || ',';
       end if;
              
       if rec_indices.index_type = 'FUNCTION-BASED NORMAL'
         and substr(rec_campos.column_name,0,4) = 'SYS_' then
         
        select column_expression
        into v_expressao
        from dba_ind_expressions
        where index_owner = p_owner
          and index_name = rec_campos.index_name
          and column_position = rec_campos.column_position;
         
        v_comando := v_comando
                     || v_expressao
                     || ' ' || rec_campos.descend;
       else
        v_comando := v_comando
                     || rec_campos.column_name
                     || ' ' || rec_campos.descend;
       end if;
    
   
end loop;

    v_comando := v_comando
                 || ');';
    
    dbms_output.put_line (v_comando);
  
  end loop;

END GERA_INDICES_CIGAM;


Executo a rotina através do SqlDeveloper e salvo a saída do DMBS_OUTPUT em arquivo para rodar no cliente:

DECLARE
  p_owner  VARCHAR2(200);
BEGIN
  p_owner := 'CIGAM';
  
  Gera_indices_cigam(p_owner => p_owner);
END;


 Observe destacado no topo da imagem que precisei aumentar o tamanho do buffer da saída DBMS, pois gera em torno de 3000 linhas de índices.



Comentários

Postagens mais visitadas deste blog

[Openfire] - Ajuste de horário no cliente Spark

Verificar uso de disco em partição ASM do Oracle 11g no Linux

[Pentaho] - Criando uma Simples transformação para apagar tabelas de um esquema no Oracle