Hoje alguém reclamou que os jobs do ambiente de homologação não estavam executando e nem apresentavam erros, abaixo a resolução:
Criei um novo job chamado JOB_TESTE para verificar se ele iria ser executado.
SQL que usei para verificar o status do job criado para testes, não retornou nenhum erro:
SET PAGESIZE 999;
SET LINESIZE 132;
col JOB_NAME format a20;
col STATUS format a10;
col RUN_DURATION format a30;
col log_date format a40;
select job_name, status, log_date, run_duration
from dba_scheduler_job_run_details
where job_name = 'JOB_TESTE'
and log_date > (sysdate-1);
Desativei a fila de jobs e o dbms_scheduler:
SQL> exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED', 'TRUE');
PL/SQL procedure successfully completed.
SQL> alter system set job_queue_processes=0;
System altered.
SQL> exec dbms_ijob.set_enabled(FALSE);
PL/SQL procedure successfully completed.
Ativei a fila de jobs e o dbms_scheduler:
SQL> exec dbms_ijob.set_enabled(TRUE);
PL/SQL procedure successfully completed.
SQL> alter system set job_queue_processes=4000;
System altered.
SQL> exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED', 'FALSE');
PL/SQL procedure successfully completed.
Verifiquei mais uma vez o histórico do job JOB_TESTE, após habilitar os jobs voltaram a funcionar:
SET PAGESIZE 999;
SET LINESIZE 132;
col JOB_NAME format a20;
col STATUS format a10;
col RUN_DURATION format a30;
col log_date format a40;
select job_name, status, log_date, run_duration
from dba_scheduler_job_run_details
where job_name = 'JOB_TESTE'
and log_date > (sysdate-1);
JOB_NAME STATUS LOG_DATE RUN_DURATION
-------------------- ---------- ---------------------------------------- ------------------------------
JOB_TESTE SUCCEEDED 08/06/24 12:00:04,164617 -03:00 +000 00:00:00
JOB_TESTE SUCCEEDED 08/06/24 12:10:04,229825 -03:00 +000 00:00:00
JOB_TESTE SUCCEEDED 08/06/24 11:57:04,039218 -03:00 +000 00:00:00