Easy way to migrate my jobs, to avoid issues for upgrade to 19c or other.<\/p>\n
I created this query to help during the process.<\/p>\n
WITH
\nqueryintervaloriginal AS (
\nSELECT job, TRUNC(MONTHS_BETWEEN(next_DATE, LAST_DATE)) AS FREQ_MONTH
\n,(TRUNC(next_DATE) – TRUNC(LAST_DATE)) AS FREQ_DAY
\n, ROUND((next_DATE – LAST_DATE)* 24 * 60) AS FREQ_MINUTE
\n, TO_CHAR(next_DATE, ‘Dy’) AS WEEKDAY_VALUE
\n, TO_NUMBER(TO_CHAR(LAST_DATE, ‘DD’)) as DAY_VALUE
\n, TO_NUMBER(TO_CHAR(LAST_DATE, ‘HH24’)) as HOUR_VALUE
\n, TO_NUMBER(TO_CHAR(LAST_DATE, ‘MI’)) as MINUTE_VALUE
\n, INTERVAL
\nFROM dba_JOBS
\n),
\nqueryinterval AS (
\nSELECT job, \/* Created by Andre Rocha *\/
\nnvl(
\nCASE FREQ_MONTH
\nWHEN 1 THEN
\n‘FREQ=MONTHLY;’ || ‘BYMONTHDAY=’|| day_value || ‘;’ ||
\n‘BYHOUR=’|| hour_value || ‘;’ || ‘BYMINUTE=’ || minute_value || ‘;’
\nELSE
\nCASE freq_day
\nWHEN 7 THEN
\n‘FREQ=WEEKLY;’ || ‘BYDAY=’|| weekday_value || ‘;’
\n|| ‘BYHOUR=’|| hour_value || ‘;’ || ‘BYMINUTE=’ || minute_value || ‘;’
\nWHEN 1 THEN
\n‘FREQ=DAILY;’ || ‘BYHOUR=’|| hour_value || ‘;’ || ‘BYMINUTE=’ || minute_value || ‘;’
\nELSE
\nCASE
\nWHEN FREQ_MINUTE=60 THEN
\n‘FREQ=HOURLY;’ || ‘INTERVAL=1;’
\nWHEN FREQ_MINUTE<60 THEN ‘FREQ=MINUTELY;’ || ‘INTERVAL=’ || FREQ_MINUTE || ‘;’ END END END,INTERVAL) intervalvalue FROM queryintervaloriginal ), queryjobs AS ( select \/* Created by Andre Rocha *\/ ‘dbms_scheduler.create_job( ‘ || chr(10) || ‘ job_name=>’||””||'”‘ || schema_user ||'”.”‘|| substr(upper(translate(what,’.(),;’,’_’)),1,20) ||’_JOB”‘ ||””||’, ‘ || chr(10)
\n|| ‘ job_type=> ‘||””||’PLSQL_BLOCK’||””||’,’ || chr(10)
\n|| ‘ job_action=> ‘||””||'”begin ‘ || what || ‘ end;”‘||””||’, ‘ || chr(10)
\n|| ‘ start_date => to_timestamp(‘||””|| to_char(next_date,’mm\/dd\/yyyy hh24:mi:ss’)
\n||””||’,’||””|| ‘mm\/dd\/yyyy hh24:mi:ss’ ||””||’), ‘ || chr(10)
\n|| DECODE (interval,’null’,NULL, ‘ repeat_interval => ‘ ||””||
\n(SELECT a.intervalvalue FROM queryinterval a WHERE a.job = dba_jobs.job)
\n|| ””||’, ‘ || chr(10) )
\n|| ‘ enabled => true, auto_drop=> false, ‘ || chr(10)
\n|| ‘ comments => ‘||””||’Converted from job ‘ || job || ”” || chr(10)
\n|| ‘);’ || chr(10)
\nfrom dba_jobs
\nwhere broken = ‘N’
\n)
\nSELECT * FROM queryjobs ;<\/p>\n","protected":false},"excerpt":{"rendered":"
Easy way to migrate my jobs, to avoid issues for upgrade to 19c or other. I created this query to help during the process. WITH queryintervaloriginal AS ( SELECT job, TRUNC(MONTHS_BETWEEN(next_DATE, LAST_DATE)) AS FREQ_MONTH ,(TRUNC(next_DATE) – TRUNC(LAST_DATE)) AS FREQ_DAY , … Continue lendo