Criação e manipulação de jobs no Oracle

É comum necessitarmos agendar algum tipo de tarefa, seja para a execução de algum comando que precise ser realizado fora de expediente, seja para a criação de rotinas para a coleta de informação ou qualquer finalidade que seja útil para o nosso dia-a-dia.

Para automatizar tarefas o Oracle possui dois recursos o DBMS_JOBS e DBMS_SCHEDULER. Ambas são alternativas excelentes, de maneira sucinta o DBMS_SCHEDULER foi introduzido à partir da versão 10g e possui diversos recursos avançados com a criação de janelas, classes, lightweight jobs, integração com o Resource Manager e etc., enquanto o DBMS_JOBS é um recurso mais antigo, mas ainda amplamente utilizado.

O presente artigo irá abordar as principais características do DBMS_SCHEDULER que acredito compreenderem a maioria das situações encontradas no dia-a-dia, caso queira expandir o conhecimento referente ao assunto sugiro fortemente o artigo da Oracle e o livro preparatório para o exame 1Z0-053 "OCP Oracle Database 11 - Adminstração II".

O comando

No exemplo a seguir iremos criar uma job com o nome "NOME_JOB", do tipo "STORED_PROCEDURE", indicando que a execução se dará por meio de uma procedure "PRC_INSERE_TABELA", a job iniciará imediatamente "SYSDATE" e será executada de a cada hora.

BEGIN

DBMS_SCHEDULER.CREATE_JOB
(
	JOB_NAME => 'NOME_JOB',
	JOB_TYPE => 'STORED_PROCEDURE',
	JOB_ACTION => 'PRC_INSERE_TABELA',
	START_DATE => SYSDATE,
	REPEAT_INTERVAL => 'FREQ=HOURLY;INTERVAL=1',
	ENABLED => TRUE,
	COMMENTS => 'INSERE A LETRA A DE HORA EM HORA NA TABELA T_TESTE_SCHEDULER.'
);

END;

O objetos necessários para a execução do exemplo anterior são a tabela T_TESTE_SCHEDULER e a procedure PRC_INSERE_TABELA.

CREATE TABLE T_TESTE_SCHEDULER (CARACTERE VARCHAR2(200));

CREATE OR REPLACE PROCEDURE PRC_INSERE_TABELA IS
BEGIN
	INSERT INTO T_TESTE_SCHEDULER VALUES ('Z');
	COMMIT;
END;

A alternativa equivalente com o pacote DBMS_JOBS pode ser observada com o comando a seguir:

DECLARE
	JOBNO NUMBER;
BEGIN
	DBMS_JOB.SUBMIT(JOBNO, 'BEGIN PRC_INSERE_TABELA; END;', SYSDATE, 'SYSDATE+1/24/60');
	COMMIT;
END;

A explicação

Na seção anterior criamos uma JOB denominada "NOME_JOB" e conforme pode ser observado os parâmetros são praticamente auto-explicativos. Contudo, alguns aspectos precisam ser melhor detalhados para um melhor entendimento do recurso.

JOB_TYPE/JOB_ACTION
A job precisa de uma definição do quê precisa executado. As opções JOB_TYPE e JOB_ACTION trabalham em conjunto e respectivamente se referem ao tipo e ação executada pela job. Para o parâmetro JOB_ACTION temos as seguintes opções:

  • PLSQL_BLOCK - Pode-se colocar uma instrução SQL ou um bloco PL/SQL na opção JOB_ACTION, normalmente utilizado para pequenas sequências de comandos;
  • ...
    JOB_TYPE => 'PLSQL_BLOCK',
    JOB_ACTION => 'INSERT INTO T_TESTE_SCHEDULER VALUES ('Z');',
    ...
    
  • STORED_PROCEDURE - Utilizado para referenciar uma procedure/function pré-existente, como utilizado no exemplo da seção anterior;
  • ...
    JOB_TYPE => 'STORED_PROCEDURE',
    JOB_ACTION => 'PRC_INSERE_TABELA',
    ...
    
  • EXECUTABLE - Pode ser utilizado para executar um script Windows ou Linux presente no servidor
  • ...
    EXECUTABLE  => 'STORED_PROCEDURE',
    JOB_ACTION => '/home/oracle/LimpezaArquivosIntegracao.sh',
    ...
    

REPEAT_INTERVAL
A job precisa de uma definição de quando o comando precisa executado. Existem frequências e especificadores pré-definidos que juntamente com o intervalo fornecem uma ampla gama de opções de agendamento.

  • Frequência - SECONDLY, MINUTELY, HOURLY, DAILY, WEEKLY, MONTHLY e YEARLY
  • Especificadores - BYSECOND, BYMINUTE, BYHOUR, BYMONTHDAY, BYWEEKNO, BYMONTH e BYYEARDAY;
  • 'FREQ=MINUTELY;INTERVAL=30' -- a cada trinta minutos
    'FREQ=DAILY;INTERVAL=2' -- a cada dois dias
    

ENABLED
É importante passar essa opção para ativar a JOB, em muitos artigos que li (inclusive da Oracle) essa opção era omitida, porém em meus testes a job simplesmente não era chamada, a não ser que eu executasse o comando para habilitá-la explicitamente.

Principais operações

Alterar a JOB
Para alterar uma JOB deve-se utilizar o método SET_ATTRIBUTE, sendo que para isso deve-se definir o nome da job, o atributo a ser alterado e o seu novo valor. No exemplo a seguir, foi alterado a frequência da job NOME_JOB para que ela seja executada a cada dois dias.

BEGIN
  DBMS_SCHEDULER.SET_ATTRIBUTE
  (
   NAME         =>  'NOME_JOB',
   ATTRIBUTE    =>  'REPEAT_INTERVAL',
   VALUE        =>  'FREQ=DAILY; INTERVAL=2'
  );
END;

Habilitar e Desabilitar a JOB
Para habilitar e desabilitar uma determinada job basta apenas utilizar respectivamente os métodos ENABLE e DISABLE como se segue.

BEGIN
 DBMS_SCHEDULER.ENABLE('NOME_JOB');
END;

BEGIN
  DBMS_SCHEDULER.DISABLE('NOME_JOB');
END;

Executar e parar a JOB
Para executar e parar uma job deve-se utilizar os métodos RUN_JOB e STOP_JOB da biblioteca DBMS_SCHEDULER, sendo que é possível passar uma listagem de jobs as serem executadas e paradas com a utilização da vírgula ex: DBMS_SCHEDULER.RUN_JOB('JOB_1, JOB_2, JOB_3').

BEGIN
  DBMS_SCHEDULER.RUN_JOB('NOME_JOB');
END;

BEGIN
  DBMS_SCHEDULER.STOP_JOB('NOME_JOB');
END;

Remover a JOB
Por fim, para remover uma JOB definitivamente deve-e executar o comando como se segue.

BEGIN
  DBMS_SCHEDULER.DROP_JOB ('NOME_JOB');
END;

Referências

https://docs.oracle.com/cd/E11882_01/server.112/e25494/scheduse.htm#ADMIN034

Adauto Santos

Analista de Sistemas Senior na Unimed Regional Maringá. Formado em Informática pela Universidade Estadual de Maringá. Pós-Graduado em Administração de Banco de Dados pela Metrocamp e Business Intelligence pela Unicesumar.

WebSite: http://www.adautosantos.com.br/
LinkedIn: http://www.linkedin.com/in/adauto-santos-91278029
Facebook: https://www.facebook.com/adauto.santos.946

Envie seu comentário

Mensagem
Nome
E-mail

Comentários

Não há comentários no artigo. Seja o primeiro a comentar!