Tudo que você precisa saber sobre jobs

Para criar jobs no Banco de Dados em versões 9i ou inferiores, podemos utilizar a package DBMS_JOB.

Vamos simplificar este assunto indo direto ao ponto que você precisa. Para isso, conheca a package DBMS_JOB:

DBMS_JOB.SUBMIT()
DBMS_JOB.REMOVE()

DBMS_JOB.CHANGE()
DBMS_JOB.WHAT()
DBMS_JOB.NEXT_DATE()
DBMS_JOB.INTERVAL()
DBMS_JOB.RUN()

DBMS_JOB.SUBMIT()
—————–

Existe atualmente duas versões, SUBMIT() e ISUBMIT()

PROCEDURE DBMS_JOB.SUBMIT(
      job OUT BINARY_INTEGER,
      what IN VARCHAR2,
      next_date IN DATE DEFAULT SYSDATE,
      interval IN VARCHAR2 DEFAULT 'NULL',
      no_parse IN BOOLEAN DEFAULT FALSE);
PROCEDURE DBMS_JOB.ISUBMIT(
      job IN BINARY_INTEGER,
      what IN VARCHAR2,
      next_date in DATE DEFAULT SYSDATE
      interval IN VARCHAR2 DEFAULT 'NULL',
      no_parse in BOOLEAN DEFAULT FALSE);

A diferença entre ISUBMIT e SUBMIT é que ISUBMIT especifica um número para o job enquanto que SUBMIT retorna o número gerado

pela package DBMS_JOB.

Visualizando os jobs submitados:
——————————-

select job,last_date,next_date,interval,substr(what, 1, 50)
from   dba_jobs;

Criando um job:
————–

O número do job (se você estiver usando o SUBMIT()) será originado da sequence SYS.JOBSEQ

Para exemplificarmos a criação e manipulação de jobs, vamos utilizar uma procedure como mostrada a seguir Lembrando que é

apenas para exemplificação:

create or replace procedure teste is
        begin
          dbms_output.put_line('Oi Eder!');
        end;
/

Examplo 1:
———-

declare
      variable numero_job number;
begin
      DBMS_JOB.SUBMIT(:numero_job, 'teste;', Sysdate, 'Sysdate+1');
      commit;
end;
/

DECLARE
      numero_job   NUMBER;
BEGIN
      DBMS_JOB.SUBMIT(job  => numero_job,what => 'teste;',next_date => SYSDATE,interval  => 'SYSDATE+1/24');
      COMMIT;
END;
/

Supondo que você tenha criado esse job as 10:00. Então, a próxima vez que ele rodará, será as 11:00.

Examplo 2:
———-

declare
      variable numero_job number;
begin
      DBMS_JOB.SUBMIT(:numero_job, 'teste;', LAST_DAY(SYSDATE+1), 'LAST_DAY(ADD_MONTHS(LAST_DAY(SYSDATE+1),1))');
      commit;
end;
/

Examplo 3:
———-

VARIABLE numero_job NUMBER
BEGIN
      DBMS_JOB.SUBMIT(:numero_job,'DBMS_DDL.ANALYZE_OBJECT(''TABLE'',''CHARLIE'',''X1'',''ESTIMATE'',NULL,50);',SYSDATE,'SYSDATE + 1');
      COMMIT;
END;
/

PRINT numero_job

NUMERO_JOB

———-
45682

Examplo 4: este job foi agendado para rodar de hora em hora
———————————————————–

DECLARE
      numero_job   NUMBER;
BEGIN
      DBMS_JOB.SUBMIT(job  => numero_job,what => 'begin space_logger; end;',next_date => SYSDATE,interval  => 'SYSDATE+1/24');
      COMMIT;
END;
/

Example 5: Examplos de intervalos
———————————

‘SYSDATE + 7’                                                                                                              :extamente sete dias da última execução
‘SYSDATE + 1/48’                                                                                                        :cada meia hora
‘NEXT_DAY(TRUNC(SYSDATE), ”MONDAY”) + 15/24’                                   :toda segunda-feira as 15:00
‘NEXT_DAY(ADD_MONTHS(TRUNC(SYSDATE, ”Q”), 3), ”THURSDAY”)’  :primeira quinta-feira de cada trimestre
‘TRUNC(SYSDATE + 1)’                                                                                            :todo dia a meia noite
‘TRUNC(SYSDATE + 1) + 8/24’                                                                                :todo dia as 08:00
‘NEXT_DAY(TRUNC(SYSDATE ), “TUESDAY” ) + 12/24’                                :toda terça-feira ao meio dia
‘TRUNC(LAST_DAY(SYSDATE ) + 1)’                                                                   :primeiro dia de cada mês a meia noite
‘TRUNC(ADD_MONTHS(SYSDATE + 2/24, 3 ), ‘Q’ ) – 1/24′                              :último dia de cada trimestre as 23:00
‘NEXT_DAY(SYSDATE, “FRIDAY”) ) ) + 9/24’                                                    :cada segunda,quarta e sexta as 09:00

———————————————————————————
Examplo 6:
———-

Você tem esta procedure

create or replace procedure teste
as
      id_next number;
begin
      select max(id) into id_next from iftest;
      insert into iftest(id) values(id_next+1);
      commit;
end;
/

Suponha que, em 16 de julho as 09:26 você execute:

variable numero_job number;
begin
      DBMS_JOB.SUBMIT(:numero_job, 'teste;', LAST_DAY(SYSDATE+1), 'LAST_DAY(ADD_MONTHS(LAST_DAY(SYSDATE+1),1))');
      commit;
end;
/

select job, to_char(this_date,'DD-MM-YYYY;HH24:MI'), to_char(next_date, 'DD-MM-YYYY;HH24:MI')
from dba_jobs;

JOB TO_CHAR(THIS_DAT TO_CHAR(NEXT_DAT
---------- ---------------- ----------------
25                  07-03-2010;09:26

Suponha que, em 16 de julho as 09:38 você execute:

variable numero_job number;
begin
     DBMS_JOB.SUBMIT(:numero_job, 'teste;', LAST_DAY(SYSDATE)+1, 'LAST_DAY(ADD_MONTHS(LAST_DAY(SYSDATE+1),1))');
     commit;
end;
/

select job, to_char(this_date,'DD-MM-YYYY;HH24:MI'), to_char(next_date, 'DD-MM-YYYY;HH24:MI')
from dba_jobs;

JOB TO_CHAR(THIS_DAT TO_CHAR(NEXT_DAT
---------- ---------------- ----------------
25                  31-07-2004;09:26
26                  01-08-2004;09:38

Suponha que, em 16 de julho as 09:41 você execute:

variable numero_job number;
begin
     DBMS_JOB.SUBMIT(:numero_job, 'teste;', SYSDATE, 'LAST_DAY(ADD_MONTHS(LAST_DAY(SYSDATE+1),1))');
     commit;
end;
/

select job, to_char(this_date,'DD-MM-YYYY;HH24:MI'), to_char(next_date, 'DD-MM-YYYY;HH24:MI')
from dba_jobs;

JOB TO_CHAR(THIS_DAT TO_CHAR(NEXT_DAT
---------- ---------------- ----------------
27                  31-08-2004;09:41
25                  31-07-2004;09:26
26                  01-08-2004;09:39

Suponha que, em 16 de julho as 09:46 você execute:

variable numero_job number;
begin
     DBMS_JOB.SUBMIT(:numero_job, 'teste;', SYSDATE, 'TRUNC(LAST_DAY(SYSDATE + 1/24 ) )');
     commit;
end;
/

select job, to_char(this_date,'DD-MM-YYYY;HH24:MI'), to_char(next_date, 'DD-MM-YYYY;HH24:MI')
from dba_jobs;

JOB TO_CHAR(THIS_DAT TO_CHAR(NEXT_DAT
--------- ---------------- ----------------
27                  31-08-2004;09:41
28                  31-07-2004;00:00
25                  31-07-2004;09:26
29                  31-07-2004;00:00

————————————————————————————–

variable numero_job number;
begin
     DBMS_JOB.SUBMIT(:numero_job, 'teste;', null, 'TRUNC(LAST_DAY(SYSDATE ) + 1)' );
     commit;
end;
/

Na definição do job, use aspas simples para determinar o que será executado e sempre coloque “;” no final da definição, como

mostrado nos exemplos.

DBMS_JOB.REMOVE()
—————–

Removendo um Job da fila de execução
Para remover um job da fila, utilize a procedure REMOVE, da package DBMS_JOB.

A seguinte declaração remove o job de número 14144 da fila de execução:

BEGIN
     DBMS_JOB.REMOVE(14144);
END;
/

DBMS_JOB.CHANGE()
—————–

Neste exemplo, o job com o número 14144 é alterado para executar a cada três dias:

BEGIN
     DBMS_JOB.CHANGE(14144, NULL, NULL, 'SYSDATE + 3');
END;
/

Se você especificar NULL para WHAT, NEXT_DATE ou INTERVAL quando você executar essa procedure, significa que você NÃO quer

alterar esses parâmetros, ou seja, eles ficarão inalterados (com a configuração anterior).

DBMS_JOB.WHAT()
—————

Você pode alterar a definição de um job executando a procedure DBMS_JOB.WHAT.
O seguint exemplo altera a definição do job com número 14144:

BEGIN
     DBMS_JOB.WHAT(14144,'DBMS_DDL.ANALYZE_OBJECT(''TABLE'',''HR'', ''DEPARTMENTS'',''ESTIMATE'', NULL, 50);');
END;
/

DBMS_JOB.NEXT_DATE()
——————–

Você pode alterar a data da próxima execução de um job. Para isso, basta executar a procedure DBMS_JOB.NEXT_DATE:

BEGIN
     DBMS_JOB.NEXT_DATE(14144, SYSDATE + 4);
END;
/

DBMS_JOB.INTERVAL()
——————-

O seguinte exemplo ilustra a alteração do intervalo de execução. Para isso foi utilizado a procedure DBMS_JOB.INTERVAL:

BEGIN
     DBMS_JOB.INTERVAL(14144, 'NULL');
END;
/

execute dbms_job.interval(<job number>,’SYSDATE+(1/48)’);

Neste caso, o job não será executado novamente após ele executar com sucesso e ainda será excluído da fila de execução de

jobs.

DBMS_JOB.BROKEN()
—————–

Um job é rotulado como broken ou non-broken. O oracle não tenta executar jobs rotulados como broken.

Examplo:

BEGIN
     DBMS_JOB.BROKEN(10, TRUE);
END;
/
Examplo:

O seguinte exemplo marca o job 14144 como not broken e seta a próxima data de execução para a próxima segunda-feira:
BEGIN
     DBMS_JOB.BROKEN(14144, FALSE, NEXT_DAY(SYSDATE, 'MONDAY'));
END;
/
Examplo:
exec DBMS_JOB.BROKEN( V_JOB_ID, true);

Examplo:

select JOB into V_JOB_ID from DBA_JOBS where  WHAT like '%SONERA%';
DBMS_SNAPSHOT.REFRESH( 'SONERA', 'C');
DBMS_JOB.BROKEN( V_JOB_ID, false);

correção de jobs brokens
————————

CREATE OR REPLACE PROCEDURE corrige_job
    AS
     /*
      || executa DBMS_JOB.BROKEN para tentar e seta
      || qualquer job broken como unbroken
     */

     /* seleciona os cursores de jobs brokens do usuário*/
     CURSOR broken_jobs_cur IS
       SELECT job FROM user_jobs WHERE broken = 'Y';
BEGIN
     FOR job_rec IN broken_jobs_cur
       LOOP
          DBMS_JOB.BROKEN(job_rec.job,FALSE);
     END LOOP;
END job_fixer;

DBMS_JOB.RUN()

—————

BEGIN
     DBMS_JOB.RUN(14144);
END;
/

Além disso, ainda podemos monitorar os jobs com a view DBA_JOBS. Vejam as colunas desta view:

   JOB
      Identificador do Job.
   LOG_USER
      Usuário que estava logado quando o job foi criado.
   PRIV_USER
      USER cujo privilégio default privileges é aplicado no job.
   SCHEMA_USER
      Essa coluna é interessante.... Se nessa coluna estiver o valor JOSE e o job executar, por exemplo, uma procedure chamada CALCULA_VALOR, o Oracle irá executar JOSE.CALCULA_VALOR.
   LAST_DATE
      Última vez, em data, na qual o job foi executado com SUCESSO!
   LAST_SEC
      Por incrível que pareça, o mesmo que LAST_DATE.
   THIS_DATE
      Se esta coluna estiver nula significa que o job não está sendo executado mas se contiver alguma data, significa que esse é o momento em que o job iniciou e ainda está sendo executado.
   THIS_SEC
      De novo, é o mesmo que THIS_DATE.
   NEXT_DATE
      Próxima data em que o job irá executar.
   NEXT_SEC
      Eu não sei porque mas, novamente, é o mesmo que NEXT_DATE.
   TOTAL_TIME
      Tempo total gasto, desde o princípio, pelo sistema, neste job.
   BROKEN
      Se Y, o job não está executando.
   INTERVAL
      Uma função de data que define o NEXT_DATE. Essa coluna determina a frequencia em que o job será executado.
   FAILURES
      Exibe quantas vezes o job executou e falhou desde a última execução bem sucedida.
   WHAT
      O que esse job irá executar. Pode ser um bloco anônimo, uma procedure ou uma package.
   NLS_ENV
      Parâmetros de sessão descrevendo o ambiente NLS para o job.
   MISC_ENV
      Outros parâmetros de sessão para a execução do job.
   INSTANCE
      ID da instância que pode executar o job. O default é 0 mas em ambiente clusterizado, isso pode mudar.

E como podemos alterar um job de outro usuário? Por exemplo, executamos a seguinte consulta: select job, log_user from dba_jobs  e obtemos uma linha com 123 e JOSE. Isso significa que o número do job é 123 e ele pertence ao schema JOSE. Mas suponhamos que estamos conectados no schema MARIA que tem permissão de DBA, para simplificar, e tentamos alterar o NEXT_DATE desse job. Certamente, a primeira tentativa seria executar o comando:

  exec dbms_job.next_date(job => 123,next_date => to_date('06/10/2011 01:00','dd/mm/rrrr hh24:mi'));

Mas teremos o seguinte erro:

ERROR at line 1:
ORA-23421: job number 123 is not a job in the job queue
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_IJOB", line 529
ORA-06512: at "SYS.DBMS_JOB", line 171
ORA-06512: at line 1

Esse erro nos diz que o usuário que executou o comando não é dono do job. E agora José? Como eu posso alterar o job do schema JOSE se, supostamente não tenho acesso à esse schema, por um motivo qualquer? Não se desespere pois um pacote não documentado está prestes à surgir, é o DBMS_IJOB. Esse cara pode fazer tudo……

Espero que tenha ajudado…