Criação e manipulação de jobs no Oracle
Postado por Adauto Santos
É 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
