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;
DECLARE
p_owner VARCHAR2(200);
BEGIN
p_owner := 'CIGAM';
Gera_indices_cigam(p_owner => p_owner);
END;
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