Menu de navegação

[airoospParticipante]

Boa tarde,

Estou fazendo alguns testes para verificar qual a melhor alternativa para testar backup. Em um servidor de testes foi executada a procedure abaixo com o objetivo de remover do banco apenas objetos dos usuários que serão testados no backup.
O problema foi que a procedure abaixo demorou bastante para ser executada e a execução do imp.exe também demorou mesmo para usuários com poucos objetos.

Alguém pode verificar se há alguma coisa errada na procedure abaixo? A mesma faz uma verificação e desabilita as constraints das tabelas antes de executar o truncate. Ambiente utilizado banco 9i.

A execução da procedure pode ter causado algum impacto na importação dos objetos?

create or replace procedure prc_limpa_objetos

is

— Airton
— Criada em: 27.08.2010
—
— Purpose:
— Esta procedure é utilizada para remover objetos do banco de dados e truncar as tabelas que não
— pertencem aos usuários:
— (CTXSYS,HR,MDSYS,ODM,ODM_MTR,OE,OLAPSYS,ORDPLUGINS,ORDSYS,OUTLN,PM,PUBLIC,QS,QS_ADM,QS_CBADM,
— QS_CS,QS_ES,QS_OS,QS_WS,RMAN,SH,SPOTNEW,SYSADM,SYSSQL,SYS,SYSTEM,XDB,WKSYS,WMSYS).
—
— Objetos que não são apagados:
— (CLUSTER,CONSUMER GROUP,CONTEXT,DATABASE LINK,DIMENSION,EVALUATION CONTEXT,JAVA CLASS,JAVA DATA,JAVA RESOURCE,
— JAVA SOURCE,LOB,MATERIALZED VIEW,OPERATOR,QUEUE,RESOURCE PLAN,RULE SET,TABLE,TABLE PARTITION,TYPE,TYPE BODY,
— XML SCHEMA)
—
— MODIFICATIONS
— Modified By Date Comments
— ————– ———- ——————————————-

— Variable Name Data Type

lnZ integer;
lnY integer;
v_linha_comando varchar2(200);

v_owner dba_constraints.owner%type;
v_nome_tabela dba_constraints.table_name%type;
v_nome_constraint dba_constraints.constraint_name%type;

v_owner_p all_constraints.owner%type;
v_nome_tabela_p all_constraints.table_name%type;
v_nome_constraint_p all_constraints.constraint_name%type;

— variáveis para o tratamento de erro
v_errorcode number := 0;
v_errortext varchar2(200) := null;

— Cursores

Cursor Lista_Constraints_Nao_Primaria (p_Owner in varchar2, p_NomeTabela in varchar2) is

select owner, table_name, constraint_name, constraint_type, status
from dba_constraints
where owner = p_Owner
and constraint_type <> ‘P’
and table_name = p_NomeTabela
order by owner, table_name, constraint_name, constraint_type, status;

Cursor Lista_Constraints_Primaria (p_OwnerTabela in varchar2, p_Tabela in varchar2) is

select owner, table_name, constraint_name
from dba_constraints
where r_constraint_name = (select constraint_name
from dba_constraints
where table_name = p_Tabela
and constraint_type = ‘P’)

and owner = p_OwnerTabela;

PROCEDURE TRATA_ERRO(
pNome_processo in varchar2,
pCodigo_do_erro in varchar2,
pMensagem_erro in varchar2,
pLinha_Comando in varchar2
)
AS
BEGIN

begin
insert into system.captura_erro(processo, codigo_erro, descricao_erro, linha_comando, data_execucao)
values(pNome_processo, pCodigo_do_erro, pMensagem_erro, pLinha_Comando, sysdate);
Commit;
end;

END TRATA_ERRO;

begin

for x in (select owner, table_name
from dba_tables
where owner not in (‘CTXSYS’,’HR’,’MDSYS’,’ODM’,’ODM_MTR’,’OE’,’OLAPSYS’,’ORDPLUGINS’,’ORDSYS’,’OUTLN’,’PM’,’PUBLIC’,’QS’,’QS_ADM’,’QS_CBADM’,
‘QS_CS’,’QS_ES’,’QS_OS’,’QS_WS’,’RMAN’,’SH’,’SPOTNEW’,’SYSADM’,’SYSSQL’,’SYS’,
‘SYSTEM’,’XDB’,’WKSYS’,’WMSYS’)
order by owner, table_name

) loop

for lnZ in Lista_Constraints_Nao_Primaria(x.owner, x.table_name) loop

v_owner := lnZ.Owner;
v_nome_tabela := lnZ.Table_Name;
v_nome_constraint := lnZ.Constraint_Name;

v_linha_comando:=’alter table ‘ || v_owner ||’.’|| v_nome_tabela || ‘ disable constraint ‘ || v_nome_constraint;

begin

execute immediate v_linha_comando;

EXCEPTION
WHEN OTHERS THEN
begin
v_errorcode:=SQLCODE;
v_errortext:=SUBSTR(SQLERRM,1,200);
trata_erro(‘desabilita constraint não primária’, v_errorcode, v_errortext, v_linha_comando);
end;
end;

end loop;

v_linha_comando:=”;

for lnY in Lista_Constraints_Primaria(x.owner, x.table_name) loop

v_owner_p := lnY.Owner;
v_nome_tabela_p := lnY.Table_Name;
v_nome_constraint_p := lnY.Constraint_Name;

v_linha_comando:=’alter table ‘ || v_owner_p ||’.’|| v_nome_tabela_p || ‘ disable constraint ‘ || v_nome_constraint_p;

begin

execute immediate v_linha_comando;

EXCEPTION
WHEN OTHERS THEN
begin
v_errorcode:=SQLCODE;
v_errortext:=SUBSTR(SQLERRM,1,200);
trata_erro(‘desabilita constraint primária’, v_errorcode, v_errortext, v_linha_comando);
end;
end;

end loop;

begin
execute immediate ‘truncate table ‘||x.owner||’.’||x.table_name;

EXCEPTION
WHEN OTHERS THEN
begin
v_errorcode:=SQLCODE;
v_errortext:=SUBSTR(SQLERRM,1,200);
trata_erro(‘truncate na tabela’, v_errorcode, v_errortext, x.owner||’.’||x.table_name);
end;
end;

end loop;

v_linha_comando:=”;

for x in (select object_name, owner, object_type
from dba_objects
where owner not in (‘CTXSYS’,’HR’,’MDSYS’,’ODM’,’ODM_MTR’,’OE’,’OLAPSYS’,’ORDPLUGINS’,’ORDSYS’,’OUTLN’,’PM’,’PUBLIC’,’QS’,’QS_ADM’,’QS_CBADM’,
‘QS_CS’,’QS_ES’,’QS_OS’,’QS_WS’,’RMAN’,’SH’,’SPOTNEW’,’SYSADM’,’SYSSQL’,’SYS’,
‘SYSTEM’,’XDB’,’WKSYS’,’WMSYS’)
and object_type not in (‘CLUSTER’,’CONSUMER GROUP’,’CONTEXT’,’DATABASE LINK’,’DIMENSION’,’EVALUATION CONTEXT’,’JAVA CLASS’,’JAVA DATA’,’JAVA RESOURCE’,
‘JAVA SOURCE’,’LOB’,’MATERIALZED VIEW’,’OPERATOR’,’QUEUE’,’RESOURCE PLAN’,’RULE SET’,’TABLE’,’TABLE PARTITION’,’TYPE’,’TYPE BODY’,
‘XML SCHEMA’)
) loop

begin

v_linha_comando:=’drop ‘||x.object_type||’ ‘||x.owner||’.’||x.object_name;

execute immediate v_linha_comando;

EXCEPTION
WHEN OTHERS THEN
begin
v_errorcode:=SQLCODE;
v_errortext:=SUBSTR(SQLERRM,1,200);
trata_erro(‘desabilitando objeto’, v_errorcode, v_errortext, v_linha_comando);
end;
end;

end loop;

end prc_limpa_objetos;

Obrigado.

Airton

[jspaulonciParticipante]

Airton, deixa eu entender, vc está rodando uma proc para truncar as tabelas, desabilitar as constraints, depois vc faz um imp ?

Qual é o tamanho da sua base ?

Você possui triggers de auditoria habilitada ?

[airoospParticipante]

Bom dia,

Não há triggers de auditoria e o processo que estou fazendo é esse mesmo desabilitar constraints e depois importar.
O arquivo DUMP gerado tem +/- 31GB, serão importados os objetos que pertencem aos sistemas totalizando 10 owners.

Obrigado.

Airton

[jspaulonciParticipante]

Vc sabe em qual parte está demorando mais ?

[airoospParticipante]

Não consegui identificar qual o momento que demora mais, acredito que seja no processo que desabilita as constraints de FK e depois PK.

Para que seja feito o truncate das tabelas é necessário desabilitar as constraints, certo?

Obrigado.

Airton

[jspaulonciParticipante]

Antes de agir precisa identificar o que está exatamente lento
Coloque no seu script um log e o tempo inicial de final de cada comando.

[Autor]

Posts