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…
