A dica de hoje é simples, mas pode te ajudar a mover os jobs do DBMS_JOB para DBMS_SCHEDULER, até a presente data o DBMS_JOB é suportado para mater a compatibilidade com versões antigas, a Oracle recomenda que você migre de DBMS_JOB para Oracle Scheduler, com essa mudança você terá uma ampla gama de recursos e melhorias no acompanhamento de execução e logs dos jobs.
O Andre Rocha desenvolveu um script para ajudar nessa migração, fiz apenas alguns pequenos ajustes e estou postando com o link de referência do blog dele: https://www.soudba.com.br/?p=1331
/*
Reference link: Andre Rocha
https://www.soudba.com.br/?p=1331
*/
WITH
queryintervaloriginal AS (
SELECT job,
TRUNC(MONTHS_BETWEEN(next_date, last_date)) AS freq_month,
(TRUNC(next_date) - TRUNC(last_date)) AS freq_day,
ROUND((next_date - last_date) * 24 * 60) AS freq_minute,
TO_CHAR(next_date, 'Dy') AS weekday_value,
TO_NUMBER(TO_CHAR(last_date, 'DD')) AS day_value,
TO_NUMBER(TO_CHAR(last_date, 'HH24')) AS hour_value,
TO_NUMBER(TO_CHAR(last_date, 'MI')) AS minute_value,
interval
FROM dba_jobs
),
queryinterval AS (
SELECT job,
NVL(
CASE freq_month
WHEN 1 THEN
'FREQ=MONTHLY;BYMONTHDAY=' || day_value || ';BYHOUR=' || hour_value || ';BYMINUTE=' || minute_value || ';'
ELSE
CASE freq_day
WHEN 7 THEN
'FREQ=WEEKLY;BYDAY=' || weekday_value || ';BYHOUR=' || hour_value || ';BYMINUTE=' || minute_value || ';'
WHEN 1 THEN
'FREQ=DAILY;BYHOUR=' || hour_value || ';BYMINUTE=' || minute_value || ';'
ELSE
CASE
WHEN freq_minute = 60 THEN 'FREQ=HOURLY;INTERVAL=1;'
WHEN freq_minute < 60 THEN 'FREQ=MINUTELY;INTERVAL=' || freq_minute || ';'
END
END
END,
interval
) AS intervalvalue
FROM queryintervaloriginal
),
queryjobs AS (
SELECT job,
TRUNC(MONTHS_BETWEEN(next_date, last_date)) AS freq_month,
(TRUNC(next_date) - TRUNC(last_date)) AS freq_day,
ROUND((next_date - last_date) * 24 * 60) AS freq_minute,
TO_CHAR(next_date, 'Dy') AS weekday_value,
TO_NUMBER(TO_CHAR(last_date, 'DD')) AS day_value,
TO_NUMBER(TO_CHAR(last_date, 'HH24')) AS hour_value,
TO_NUMBER(TO_CHAR(last_date, 'MI')) AS minute_value,
interval,
'BEGIN ' || CHR(10) ||
' DBMS_SCHEDULER.CREATE_JOB( ' || CHR(10) ||
' job_name => ''' || schema_user || '.' || SUBSTR(UPPER(TRANSLATE(REPLACE(REPLACE(what,' ',''),'BEGIN',''), '.(),;', '_')), 1, 20) || '_JOB'',' || CHR(10) ||
' job_type => ''PLSQL_BLOCK'',' || CHR(10) ||
' job_action => ''BEGIN ' || REPLACE(what, '''', '''''') || ' END;'',' || CHR(10) ||
' start_date => TO_TIMESTAMP(''' || TO_CHAR(next_date, 'MM/DD/YYYY HH24:MI:SS') || ''', ''MM/DD/YYYY HH24:MI:SS''),' || CHR(10) ||
' repeat_interval => ''' || (
SELECT a.intervalvalue
FROM queryinterval a
WHERE a.job = dba_jobs.job
) || ''',' || CHR(10) ||
' enabled => TRUE,' || CHR(10) ||
' auto_drop => FALSE,' || CHR(10) ||
' comments => ''Converted from job ' || job || '''' || CHR(10) ||
' );' || CHR(10) ||
'END;' AS migration_script
FROM dba_jobs
WHERE broken = 'N'
)
SELECT job,freq_month,freq_day,freq_minute,weekday_value,day_value,hour_value,minute_value,interval,REPLACE(REPLACE(migration_script,'BEGIN BEGIN','BEGIN'),'END; END;',' END;') migration_script FROM queryjobs;